Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 16, 2022 10:36 PM
Hello all, my Friday afternoon brain just can’t get my head around this. I have a large database set up for our members and need to add new functionality to it. I have duplicated the base to try and nut it out.
I need a column that is for ‘past groups’, of which people could have been in several. I then need another column that relates to current or non current member based on these groups.
So Member 1 was in group 1, group 2, group 3
Member 2 was in group 2, group 3, group 4
Member 3 was in group 4
if group 1 ended 2016, group 2 ended 2020, group 3 ends 2022 and group 4 ends 2023
So the current member should only be showing yes if the member is in either group 3 or 4
I am not even sure if that makes sense. I need to set up a view for current members but don’t want to manually change each, I need a way to automate it based on the group they are in.
I have tried looking at classroom-type databases, but just can’t work out how to automate that current & past option based on a group date.
Solved! Go to Solution.
Jun 17, 2022 11:09 PM
Hi Kel, yeap, we would just need to modify the formula field to something like
IF(
IS_AFTER(TODAY(), {Running Date}),
0,
1
)
You can duplicate the base to view all the formulas that were used!
I’ve also updated the original base to use dates like you wanted, so you can check that out if you want!
Jun 16, 2022 10:46 PM
Hey Kel, hmm, I’m not entirely sure whether I understand this, so apologies in advance if I’ve got something wrong
Check this base out
Is this sort of what we’re trying to achieve?
Jun 17, 2022 03:28 AM
Hi Adam, thanks for your reply and the example. Yep, that is the type of thing I am after, I was trying to achieve something using dates. So the running dates of the groups determine if they are active or not. We have a membership that is relative to the running groups/shows. So people are only active members if they are in a current show or they are a life member. If that makes sense.
So did you use formulas in that base? Could it be made dependent on a date?
Jun 17, 2022 11:09 PM
Hi Kel, yeap, we would just need to modify the formula field to something like
IF(
IS_AFTER(TODAY(), {Running Date}),
0,
1
)
You can duplicate the base to view all the formulas that were used!
I’ve also updated the original base to use dates like you wanted, so you can check that out if you want!
Jun 18, 2022 03:23 PM
Thank you again Adam. This is awesome :slightly_smiling_face:
Jun 20, 2022 05:13 PM
Hi @Adam_TheTimeSavingCo
Sorry just wanted to quickly revisit this. I Would also like to have a ‘teacher’ column for this.
So in the top screenshot, the group tab, there would also be a list of teachers alongside members, all relating back to the member list.
I got this working by doing another linked field. However, when using the roll-up it only shows active or inactive for the member column not the teacher column. If that makes sense?
Check Here to see my example based on your previous help.
Thank you again :slightly_smiling_face:
Jun 20, 2022 11:17 PM
Hi Kel, could you tell me more about why you need it to be in a separate linked field? What issues do we face when using a single select field to denote whether the person is a member or a teacher?
Jun 21, 2022 02:14 AM
In this instance, I am using it for cast and production teams. Sometimes a member can be in the cast of one show, and then at a later date be on the production team of another. So I don’t want to give a specific type to each member.
So in the example I linked to (an edit of what I copied from you originally), a member could be either a teacher or a participant.
And the inactive based on Date only seems to be carrying over for the column of members, not the teachers.
In the instance of Member 1, they were in group 1 & 2 which are now inactive, but now they are a teacher in Group 4, which is active. However they are still showing as inactive on the main table.
Does that make sense?
Jun 21, 2022 02:31 AM
Hi Kel, ahh, got it, thank you for the explanation.
I’ve updated the original base to handle this. We would need to have one rollup field per linked field, and then use a formula field to check the values of them both.
If you’re open to it, I would suggest creating a new table called Participation
or some such, where you input participation data. We’d get the same outcome, and it would be a clearer set up that would allow you to add more details per participation if you needed to.
I’ve set it up here for you to have a look at.
Jun 21, 2022 03:10 PM
Thank you again! Brilliant. The first edit does exactly what I need, but will also look over the other offering. Thanks so much :slightly_smiling_face: