How to initially set field contents and not allow it to change

[Lynda_Stringer]

I am tracking badges earned each year for a kids club. I have a member table that contains info about each member, such as what grade and club they are currently in. I plan to create a Badge table that will contain the names and dates for badges earned. It needs to have a separate record for each year for each child.

When I create a record for a child this year, I want to use the current year, the club, and the name to generate a sort field for the record. Next year I want to be able to do the same, but the year and possibly the club will be different. I don’t want the sort field in previous records to change.

Is there a way to do this without having to manually enter the sort field info?

[ScottWorld]

Text, number, and currency fields give you the ability to choose a default value.

Go into “customize field type” for your text/number/currency field, and you can type in a default value there.

[Lynda_Stringer]

That won’t work for what I’m trying to do. Here are some examples for the sort field in the Badge table:

THIS YEAR

20205.3Tina (other fields)
20203.2Cari (other fields)

NEXT YEAR

20216.1Tina (other fields)
20213.3Cari (other fields)
20212.1Jen (other fields)

The 2020 and 2021 come from a current year field in another table.
The next numbers come from the club field in the Member Table as does the name.

The default that you described would be the same for every record. Yes I could manually input the info, but it would take extra time to enter it for every new record each year. I need to be able to auto generate the keys based on the current member and year, then next year do the same without changing the key for the previous year.

[kuovonne]

It sounds like you want a formula field that will combine information from the current table and from a linked table.

You can pull the year into the current table using a rollup field that picks the maximum year/date from the linked table.

Then use a formula field to combine all the values that you want.

[Lynda_Stringer]

Since I’m new to this particular app, I may have done something wrong, but when the year is changed the year in the Badges record also changes.
I have 3 tables:
Main - contains 1 record which contains the date that is changed at the beginning of our club year
Member - contains a record for each member and each leader along with info like birthdate, grade, club, …
Badges - will contain a record for each member and for each year (i.e. Tina has a record for 2019 and one for 2020, …). The year, club code, and name are concatenated into Sort.

I decided to create a 2nd year (year2) field containing a formula with a test: if year2 is blank() then load year into it otherwise leave it alone. The formula has a format error so I know it’s not correct

IF(srt2=BLANK(),CONCATENATE(srtyr&cl),)

And I’m not even sure if the formula can test the field it is in. If not, maybe I can create another field that gets loaded with a value if year2 has a value.

Any suggestions on the best way to handle this?

[Lynda_Stringer]

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.
IF(srt2=BLANK(),CONCATENATE(srtyr&"-"&ClubN&name),srt2)

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.

[kuovonne]

Could you include some screen shots to make it easier to understand your situation?

However, formula fields and rollup fields might not work the way you want. They are always automatically calculated. It isn’t possible for a formula field to calculate a value, and then stop calculating it.

Airtable can have a button that will calculate a value and store that calculated value in a non-formula field. However, that requires Scripting Block, which will become a paid feature in September.

[Lynda_Stringer]

Here is the screen shot with certain places marked.
The name and club code came from Members; the date came from Menu.

[kuovonne]

How did you create the screen shot of what you want to end up with? Is it an actual screen shot, or did you manually create it by pasting together different images?

Because you are changing the date in the original record in the [MENU] table, this change gets propagated through the linked records and formulas. You cannot change this behavior. You need a different base design.

It is difficult to tell how you should change the base because your screen shots don’t show all of the relationships among the tables. However, one option to consider is to create a new record for each year, instead of changing the existing record.

[Lynda_Stringer]

Sorry I couldn’t respond sooner. My area was without power for awhile, then I had to do some major changes to a non-profit website I maintain.

I manually created the screenshots.

The lnkmenu field links to the menu database which contains one record containing TX and each record in Badges contains TX and the date is pulled from there. The srt field links to a record in the Member database and pulls the name and club. There is one record in Members potentially linked to several records in Badges. If I do use more than one record in Menu, should I sort it newest first or last or does it matter to the way it would function internally? The newest at the top would easiest for accessing.
Any other ideas or suggestions?