Create a field to collect dates from other fields 1

[hockeydad45]

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?

 

[JeanW]

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.

 

[hockeydad45]

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.

 

[JeanW]

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.

 

[hockeydad45]

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.

 

[JeanW]

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

 

[JeanW]

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...