Adding Specific Numbers From Drop-Down

[RileyFitnessCen]

Hi,
I’m working on a n app to add new members and their information to our fitness center database. Our lock has a key pad and we have to enter 100 unique 5-digit codes all at once at the beginning of the year. However, we get new members all throughout the year.

How can I assign each new member one of the access codes, selected from a predetermined list WITHOUT it being a duplicate of a number already assigned?

[Bellave_Jayaram]

You can store the codes in a column called Code and a Yes/No a column called Assigned; set it No to start with and change it to Yes when you add a new member. You could pick the first unassigned code for each new member using a formula such as INDEX(SELECT(Table[Code],[Assigned]=FALSE),1).

[RileyFitnessCen]

@Bellave_Jayaram

Will I need to manually change [Assigned] from “No” to “Yes” when I add a new member, or is there a way to do it automatically?

[Bellave_Jayaram]

It can be done via an action (which is set to Do not Display) which runs when you add a new member but since it is another table (than the one you are adding a row to), it is a little tricky. You have to set up the dropdown as a ref table and you will need an action in the Member table as well as one in the Codes table.

In the Member table action, set Do this for that action to Data: Execute an action on a set of rows where the row is selected using a SELECT formula like so:
SELECT(CodeTable[KeyColumn],[Code]=[_THISROW].[Code])

In the Codes table action, set Do this for that action to Data: Set the value of a column and change the Assigned to TRUE.

Alternatively, it could also be done using a sheet formula using a VLOOKUP.

[RileyFitnessCen]

Bellave_Jayaram:

INDEX(SELECT(Table[Code],[Assigned]=FALSE),1)

That worked beautifully!! Thank you!!