Let ( [
F1 = "SELECT Max ( Serial ) FROM Table_Name" ;
F2 = ExecuteSQL ( F1 ; Char ( 9) ; ¶ ) ;
F3 = Get ( CurrentDate ) ;
F4 = Right ( Year ( F3 ) ; 2 ) & "-" & RIght ( "0" & Month ( F3 ) ; 2 ) ;
F5 = Right ( Serial ; 3 ) ;
F6 = Left ( Serial ; 5 ) ;
F7a = "SELECT Count ( Serial ) FROM Table_Name WHERE Serial LIKE ?" ;
F7b = ExecuteSQL ( F71 ; Char ( 9 ) ; ¶ ; F4 & "%" ) ;
F7 = If ( F6 = F4 ; F5 + 1 ; If ( F7b > 0 ; F5 + 1 ; 1 ) ) ;
F8 = F4 & Right ( "000" & F7 ; 3 )
You need to change
year & "-" & month
year & month
(no quotes), NOT "year & month". year and month are variables that must not be quoted, while the quotes in "-" denote a literal string (a piece of text).
I'm pretty sure "they" suggested the same.
Taylor, what's it with you, ExecuteSQL and those F variables? Been to some seminar lately?
Been having fun with ExecuteSQL ever since it came out in 12. It does have limitations like the "Max" call on the first "F1" is a real slow call. As for the F's.... maybe it reminds me of grades in school... <ooops>. Maybe I should use A's next time!
Yeah, we all love ESQL(), despite its limitations and implementational quirks; but it's only recently that I've seen you use this F… notation.
Maybe I should use A's next time!
Can't get worse than
If ( F6 = F4 ; F5 + 1 ; If ( F7b > 0 ; F5 + 1 ; 1 ) ) <shudder>
Once you introduce "F7b", pretty soon you'll need a lookup table to keep track of who's who in your calc …
Yeah, more descriptive variable names would be a lot better. But I just got in the habit of trying to keep variable names short (2 digits or sometimes 3).
For my paying customers that I document on... I add a "// insert comment" at the end of each line to explain what I am doing. But for freebee help here, I'm lazy.
e.g. F3 = F1 + F2 // I am adding two variables together to get their total
erolst is right about the quotes.
I'll add that you should avoid using the names of functions, like Month() and Year() as variable names.
lastId =GetNthRecord (AEfield; Get ( TotalRecordCount ) - 1);
Roll-your-own serial numbers will break in multi-user mode. This is not a safe process to use if your relationships depend upon it.
ADDED: Also, serials should be meaningless.
I agree with LaRetta about creating your own serial numbers being a problem with multiple users that create records very close to each other. I often create such fields as you describe for internal reporting purposes, but I make maker serial numbers UUIDs and use them for relationships, etc., and just don't show the users it.
I tried already to change to "year & month" no quotes but it didnt give me the correct serial for the next succeding serial.