Hello, I am building a base for tracking our club membership. Memberships have prices and expirations. For the main table, my Primary Field is the last name of the member.
Some members have been awarded Lifetime Membership for their years of service which means they don’t pay dues anymore. However, there are details about Lifetime membership that don’t apply to regular members. A simple example is the date they were awarded Lifetime.
I want to make a separate table to track the details of each Lifetime member. I discovered that the Primary Field cannot be a linked record. Therefore I cannot have the Lifetime member’s Last Name be the Primary Field unless I want to keep this table unrelated.
I’m thinking that the Primary Field could be the date the Lifetime membership was awarded but am not sure if that’s the best way to structure things.
Thoughts on this?
Many times, I make the Primary field a Formula field. You can then just input any other Field you want so that it’s that constant value. You can just type in the other Field name, then it will always be that value.
You could also CONCATENATE (join together) multiple fields with the Formula field that way you can see what you need.
2 Likes
This is great! Thank you. I’ve now concatenated the First and Last name of the members successfully and made that formula the Primary Field. I wanted to also add the Joined Date field to the concatenation to give one more protection against duplicates. However, it adds the date AND time and it becomes a very long string and rather unsightly.
1 Like
You can use DATETIME_FORMAT() to decide how to represent your date in the formula.
5 Likes
Awesome! So glad it helped, @AstroChris
As @RussellBishop mentioned, you’ll use the DATETIME_FORMAT() formula to help with this.
You could do something like this:
IF({Joined Date},DATETIME_FORMAT({Joined Date}, "MM-DD-YYYY"),"⌛")& " - " & IF(AND({First Name}), {First Name} & " " & {Last Name},BLANK())
This formula says:
if there’s a value in the Joined Date field, use the date value and format it as month-day-year, otherwise add a 
- If, and also, there’s a value in the First Name field, CONCATENATE First name and Last name with a space in between, otherwise show the field as Blank.
Which then looks like this:
Table view
Actual formula
2 Likes
