×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Create a field to collect dates from other fields

Create a field to collect dates from other fields

Create a field to collect dates from other fields

(OP)
I need to create a field that would collect dates from other fields and allow me to search the field with all the dates in it and pull up files with a date I am seraching for.

i.e.  I have created a database for tracking insertion orders for a newspaper.  They want to be able to place the order with multiple insertion dates.  The production department wants to be able to search in a single field for a single date to bring up all ads that need to be produced for that issue.

My thought would be to create a field that would pull all of the dates entered on a single order into one field that the production department could use as a single field to search.

Any ideas?

RE: Create a field to collect dates from other fields

If those dateFields are in the same record and has different names, you could make a calcfield where you concatenate all the possible dateFields in a return separated list.

If they are in different records, make a self relationship between a unique value and take that relationship to make the return separated list.

This will be the field to search on.

RE: Create a field to collect dates from other fields

(OP)
Hi JeanW,

Thank you for your timely reply.  If I may ask one more step in the process..  The dateFields are all in the same record.  When I specifiy the calculation is it

calcField =  a and b and c and d etc..?  I am not sure how to write a calculation that would return several dates (as many as 24) in one calcField.  Do you have any suggestions?

Your time is much appreciated.

RE: Create a field to collect dates from other fields

There is a caveat with dates.
FileMaker is storing dates as numbers, it's only the format on screen that is changing.
If you just concatenate the date fields, you concatenate the numbers.

Therefor you have to use the GetAsText() function (FM 7+) to concatenate, and you have to use a script (other ways are still possible) to find the dates back.

Suppose you have 4 datefields, dateA, dateB, dateC and dateD.
To concatenate these in the field allDates = calc, result text, use

dateA & ¶ &
dateB & ¶ &
dateC & ¶ &
dateD

But again, you can't search for 'dates' in this field.

You could make a script to search for a given date, something along these lines, after you made a global date field = dateSearch:

Enter Find Mode ()
Set Field (yourTable::dateA;dateSearch)
New Record/Request
Set Field (yourTable::dateB;dateSearch)
New Record/Request
Set Field (yourTable::dateC;dateSearch)
New Record/Request
Set Field (yourTable::dateD;dateSearch)
Perform Find()

Problem could be if you want to search for a specific 'unknown' date, where you have to use a daterange to find the date.

Make 2 global date fields, dateStart and dateEnd.
Make a script (Find range), something along these lines

Enter Find Mode ()
InsertCalculatedResult(Select;yourTable::dateA;GetAsDate ( yourTable::dateStart ) & "..." & GetAsDate ( yourTable::dateEnd )
New Record/Request
InsertCalculatedResult(Select;yourTable::dateB;GetAsDate ( yourTable::dateStart ) & "..." & GetAsDate ( yourTable::dateEnd )
New Record/Request
etc...
Perform Find ()

This is a lower FM 7 stripped down way.
From FM 7 on you could use the Let function to make the script shorter or make calc fields, but this is one of the possible basic ideas.

RE: Create a field to collect dates from other fields

(OP)
JeanW,

I know I am not supposed to post non-technical notes, but I am just floored by the amount of time you spent to help me with my problem.  I greatly appreciate it!  Happy New Year.

RE: Create a field to collect dates from other fields

It's a way to give back to the community...

And now I see I forgot something:

The concatenate field should be:

GetAsText ( dateA ) & ¶ &
GetAsText ( dateB) & ¶ &
GetAsText ( dateC) & ¶ &
GetAsText ( dateD)

Sorry about that. Happy New Year and Happy FileMaking

RE: Create a field to collect dates from other fields

You can do it the other way round, by using GetAsDate() to concatenate the date fields.

GetAsDate ( dateA ) & ¶ &
GetAsDate ( dateB) & ¶ &
GetAsDate ( dateC) & ¶ &
GetAsDate ( dateD)

This way you can make a search for one date in the calc field, while the syntax has to be exactly the same as the date field.
If the date is displayed like xx/xx/xxxx and you enter the search as xx-xx-xxxx, no luck...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close