1 of 1 people found this helpful
Try the Char( Number ) function. Number can range from 0 to 99999 for single characters (including punctuation and accents) and you can serial increment the Number. Char(65) returns 'A'; Char(66) returns 'B'; etc.
I took your suggestion and put together a solution that works:
I created a number-type field Job::zu__Next_Estimate_Suffix_Code__n with the auto-enter value of 65 (the code for the character "A"). I created a calculation field zc__Next_Estimate_Suffix__t with the calculation Char ( zu__Next_Estimate_Suffix_Code__n ). I created the text-type field Estimate::zu__Estimate_No_Suffix__t with an auto-enter calculation that takes its value from the field zc__Next_Estimate_Suffix__t of the parent Job table occurrence (do not replace existing value). Then I created the calculation field Estimate::zc__Estimate_No__t that concatenates the Job_No__t field from the parent Job table occurrence with Estimate::zu__Estimate_No_Suffix__t.
Once those four new fields were created I modified the script that permits the user to create a new Estimate so that, after the new Estimate record has been created, it increments the value of the field zu__Next_Estimate_Suffix_Code__n of the parent Job table occurrence by 1.
This looks to me to be somewhat inelegant and kludgy. Is there a better way?
You may be able to eliminate at least one of the fields by using:
• zu_Next_Estimate_Suffix_Code_n: Number field, auto-enter, serial number begining at 65 with 1 increment
• Estimate::zc_Estimate_No_t: text field with the auto-enter calculation = Job_no_t & Char( Estimate::zu_Next_Estimate_Suffix_Code_n )
That said, you know your context far better than I do.
The first Estimate which is created as a child of a specific Job must have the Estimate No. suffix "A". If I understand you correctly and did as you suggest the field Estimate::zu__Next_Extimate_Suffix_Code__n would be incremented every time a new Estimate record is created regardless of what Job it is related to. If that were to happen the first Estimate of some job could have the suffix "R" and after 26 Estimate records have been generated the suffix wouldn't be an upper-case alpha character at all. Am I mistaken?
As I said, you know your context (tables, relationships, layouts, requirements) better than I do. Just try to eliminate as many 'intermediate' calculation fields as possible for best performance.
Got it. Thanks.
Here's one way. The attached uses a global field (called "temp") in the related Estimates table that holds the count of records related to a particular job each time the "Click to Add Estimate" button is clicked. In the Estimates table, a field called Alpha uses the Choose function to choose the letter that matches the number in "temp." In the Alpha field, I have only gone up through the letter "M", but you will need to go further and maybe even start over at "AA" after "Z" if you anticipate more than 26 estimates related to a Job. The Job Display field then concatenates the Alpha and the id_job.
In the attached, related records are created via the script.
JOB_ID_Display.fmp12.zip 9.4 K
I like it. It's more simple than my first solution. I was totally unfamiliar with the Choose function before now so I'm farther ahead just by learning about that.
I wasn't happy about this Estimate numbering convention because of what happens if you have more than 26 Estimates for one Job. The client insists that this will never happen and is adamant that the Estimate numbering convention be maintained. Your solution addresses this issue.
Thanks very much.
Thanks. Glad I could help.
If you find yourself needing to encode postfixes that get larger than you want to deal with in a big Choose() function, you might consider this GetNumberAsRadix function, which could convert the estimate number to base 26 like this:
GetNumberAsRadix ( ~estimateNumber - 1 ; "ABCDEFGHIJKLMNOPQRSTUVWXYZ" )
Using this, estimate numbers 1, 2, 3, ... , 26, 27, 28, ... , 1000, 10000, 100000 will be A, B, C, ... , Z, BA, BB, ... , BML, OUP, FRYD, respectively. This may not be exactly what you need though, since this is treating "A" as a zero in the base 26 representation, so it will never give you any leading "A" in a multi-letter value; thus "BA" for 27 instead of "AA."
Even though you already have an answer, sounds like it is very similar to something that I'm working on, and had the same question.
Check out this thread:
Always more that one way to do things in FMP. I love the developer community here!