Assigning Sequential Text Values to Sequential Dates
I need to create a calc field that will assign the sequential text values "first" through "twelfth" to records based upon a date field. Help.
Just thought of a non scripted way to do this. Up for a self join relationship?
In Manage | Database | relationships, make a new table occurrence of YourTable by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box. We have not duplicated a table. Instead, this is a new reference to the same table already present in your database. Add it to your relationships like this:
YourTable::Date > YourTable 2::Date
Define this calculation: Choose ( Count ( YourTable 2::Date ) ; "" ; "First" ; "Second" ; "Third" ; "Fourth" ; "Fifth" ; "Sixth" )
Can you give examples of what those dates will be and how a specific date will match to a specfic text value?
The dates could be anything. if the dates are 3/1/12, 4/3/12 and 5/16/12 I need the March date to match to the word "first", the April date to match to the word "second" etc.
So they need to be named in the order of the dates when the dates are sorted in ascending order.
Is each date entered into the same field of a different record in the same table? Or are these different fields in the same table?
Are the dates always entered in increasing order? (What should happen if the dates were entered in this order: 4/3/12, 3/1/12, 5/16/12?)
Is there an upper limit? (Might the text returned one day need to be: "Five Million Three Hundred Thousand Two Hundred Sixty Fifth?")
Yes, thank you.
It is the same field used in each record.
The dates may be entered in random order, and the order that they're entered is irrelevant if I can work it out so that the text field works properly.
If the value ever gets to five million I will certainly be somewhere killing myself....so, I would say that the upper limit could safely be 24.
so, I would say that the upper limit could safely be 24.
But that means there is no set limit--it might not be in the millions but we still have to try for a solution that won't hit an upper limit and fail. That's I'm trying to get a complete picture of the scope of values needed.
The fact that the dates might be entered in any order complicates this as entering a date not only affects the text returned for the current record, potentially, it could require a change in all the other records in the table.
If you only need to see the text when the records are sorted in ascending order by date, we can set up a calculation that converts the value returned by Get ( RecordNumber ) into a word form of the number.
If you need to see these values even when records are not sorted in order, I think we'll need to use a script with Replace Field Contents to assign text to all records in your table each time you add or change a date.
I will need to see the values even when the records are unsorted, unfortunately.
Ok here goes...
I'm going to work with 6 dates, but you can expand the calculation to whatever number of text values you need.
This expression--the same one that would work as an unstored calculation if you only needed the values when the records are sorted--can be used with replace field contents in a script:
Choose ( Get ( RecordNumber ) ; "" ; "First" ; "Second" ; "Third" ; "Fourth" ; "Fifth" ; "Sixth" )
Set up your script like this:
Show All Records (perform a find instead if you want to update groups of records instead of all of them.)Sort Records [no dialog ; Restore] Replace Field Contents [no dialog; YourTable::TextField ; Choose ( Get ( RecordNumber ) ; "" ; "First" ; "Second" ; "Third" ; "Fourth" ; "Fifth" ; "Sixth" )]
OMG, the self join totally worked like a charm. You rock, thanks!
Hello again, PhilModJunk.
I've happened upon a further complication with this field that I didn't foresee, and I'm hoping you can save the day again.
I need the text values, first second etc, to re-start with the new year.
You can add a calculation field, cYear, defined as Year ( date ) to extract the year from your date field. Then you can include this field in your relationship:
YourTable::cYear = yourTable 2::cYear ANDYourTable::Date > YourTable 2::Date
Seriously, you're amazing. Thank you. Marry me.
My wife and kids would object.
Retrieving data ...