Auto-populate Field based on Check Box

How do you log the conflict dates? Which fields do you use?

You could have an Events table with Date field, and link them to the Actors. Then add a Lookup to the date and that’s it.

Expanding on Elias’ solution, feel free to simplify. I don’t know how much of your base you’ve already set up, but this is how I’d do it:

Table 1: Plays

• Text Field: Play Title (i.e Footloose)
• Lookup Field: Production Date (i.e. July 8, July 9, July 10) => {Table 2: Formula Field}

Table 2: Productions

• Date Field: Production Date (i.e. July 8)
• Link to Another Record (single select): Play (i.e. Footloose) => {Table 1: Play Title}
• Formula Field: {Play} & {Date Field} (i.e. Footloose - July 8)
• Link to Another Record (multiselect): Actors => {Table 3: Name}

Table 3: Actors

• Text Field: Name (i.e. John Smith)
• Link to Another Record (multiselect): Productions => {Table 2: Formula Field}
• Lookup Field: Conflict Dates => {Table 2: Date Field}

This should give you a single column in the Actors table with the dates of their scheduled productions, and thus the dates they are busy. This method eliminates the need for a form if you already know which Actor is in which Production.

You could filter Actors by Production (to get the relevant people who need to attend that play’s rehearsal, for instance), and then create a calendar view of the Actors table to quickly see the dates where no one has an engagement.

Kamilla,
I like how your brain is thinking here. (All actors are not in other plays, but there is a large set that is…so everybody fills out a form. But if they are in one of these known “sets” of conflicts, it would be nice if they didn’t have to enter them all, and I could have it automated for them.

So, I need some “IF” logic to look and see if “x box is checked” then apply this formula to this field. Otherwise, use whatever somebody types in.

Does this make sense?

What coding syntax do I need to study to learn how to use these formulas well!!???

Julie

I reference this frequently for writing formulas:

See the following revised base outline. With this layout, your actors can multi-select Plays from a form field which will automatically link them to a list of conflict dates, based on all that play’s production dates. Changes are in bold.

Table 1: Plays

• Text Field: Play Title (i.e Footloose)
• Lookup Field: Productions => {Table 2: Formula Field}
• Lookup Field: Production Dates => {Table 2: Date Field}
• Link to Another Record (multiselect): Actors => {Table 3: Name}

Table 2: Productions

• Date Field: Production Date (i.e. July 8)
• Link to Another Record (single select): Play (i.e. Footloose) => {Table 1: Play Title}
• Formula Field: {Play} & {Date Field} (i.e. Footloose - July 8)
• Lookup Field: Actors => {Table 1: Actors}

Table 3: Actors

• Text Field: Name (i.e. John Smith)
• Link to Another Record (multiselect): Plays => {Table 1: Play Title}
• Lookup Field: Conflict Dates => {Table 1: Production Dates}

//
Now in order to use the calendar view you’ll need to be in the Productions table, and add your filters by actor.