What I would do...
in the relationship window, create another table occurrence of your table.
Relate it to your current one, with field category = category and grade = grade.
your catno field should be text and have its calculation inside the auto-enter value, so when you create one record, it automatically create the catno. the field should not itself be a calculation.
calculation would be : Left(Category; 2) & "-" & Grade * 10 & "-" & right("000" & ( max( right(secondtableoccurrence::catno; 3)) + 1) ; 3)
Okay. I have questions about where to enter the calculation, but let's make sure I didn't miss a step with the relationship.
In the relationship window, I have "Table 1" with all of my fields listed. I created an identical table which I labled "Table 2". As I clicked Table 1 Category and drug to Table 2 Category a line formed between the two and it moved to the top of each table. I did the same with grade.
For the "CatNo" field, I changed the type to Text and hit Options & was presented with the Auto Enter tab. Now within this window, where do i put the calcuation? under Data, Calculated Value, Looked Up Value?
and should it read as follows:
Left(Category; 2) & "-" & Grade * 10 & "-" & right("000" & ( max( right(Table 2::CatNo; 3)) + 1) ; 3)
I tried it in the Calculated Value window and recieved the following error:
In the function Average, Count, Extend, GetRepetition, Max, Min, NPV, StDev, Sum, GetSummary or GetNthRecord, an expression was found where a field alone is needed.
right(Table 2::CatNo; 3) was highlighted.
After two solid days of research, reading the pertinent chapters from "the missing manual" and searching every forum post related to this topic, I have just about blown a fuse.
Can somebody please give me some more direction?
You're putting the calculation in the right place. It just has a bug that needs to be fixed.
Define a calculation field set to return "Number" as CatSerial: right ( catno; 3 )
Now modify your expression as:
Left(Category; 2) & "-" & Grade * 10 & "-" & right("000" & ( max( Table 2::CatSerial )) + 1) ; 3)
Thanks for responding PhilModJunk!
I tried your suggestion and FM has apparently taken issue with the next to last parenthesis and said
"There are too few parameters in this function"
That's because I left in an extra ) and a harmless extra ( ).
Edit it to be: Left(Category; 2) & "-" & Grade * 10 & "-" & right("000" & max( Table 2::CatSerial ) + 1 ; 3)
Wow! It Worked! I know that doesn't come as a surprise to you, but it shocked the hell out of me.
Thank You! Thank You! Thank You!
Hopefully, after some analysis, I can understand how it worked.
Here's how it works:
The second relationship, via the table 2 relationship matches to all records with the same category. The maximum function returns the largest serial number value of this set of matching records. Adding one to this maximum gives you the next number in the series for this category.
Final note: While this is a useful item to put on your music folder labels, I wouldn't use it as the link between tables in relationships. I'd use a separate auto-entered serial number field for that purpose.