I changed srt2 to load the contents of sort, then in sort I used this formula so if srt2 is blank I create the initial string, but if srt2 isn’t blank I copy the initial string back into sort.
It said it couldn’t do it because it created a circular reference. I can see why that would be true, but I don’t know how to keep the older records from changing when the date and club changes but allow new records to be set up with the new date and club.
The Main table contains 1 record and each year the date for the beginning of that club year is changed to the new year so the year advances over time. The Member table contains a record for each member and each member’s record shows the club the member is in that year. If a child joins at the age of 3, the Badges table will ultimately contain 12 to 13 records (depending on what time of year they turn 3) by the tie they graduate (1 record for each year). Each record contains the badges earned that year (up to 9 per year). Each year’s record needs to contain the year and club for that year.
So if a member is in the 2nd year of the 4th club in 2019 Sort should contain 2019, 4.2, and the member name concatenated together. That info in that record needs to remain unchanged, but a new record generated for 2020 needs to contain 2020, 4.3, and the member name.
I hope this gives a better idea of what I’m trying to do. If it isn’t possible to do this in this app, I would like suggestions of one that will work. Paying an annual fee isn’t in the budget because of the curriculum costs (we don’t charge the members a fee).
SmartList on my Palm worked perfectly to do this, unfortunately, the developer discontinued this app and it was never ported to android or PC. SmartList also, allowed me to click a “button” on a member record and go to a specific view that showed only the records for that member. I’ve tried using HandBase, but it doesn’t work the same and it doesn’t include the basic concatenation function. It seems geared more for crunching numbers than dealing with text.