Picking from Existing lists in FM

[yilbber]

So, in my database I have already been able to Create a Crew list, for a project. However, Each Call for a project can have varying or even the same crew members. How do I set it so that i can create a Check list for crew from the pool of crew already assigned for a show?

The crewxstaff contains all the crew, staff availability contains/tracks if a person is available, Crew code are their initials and each part (crew), call, Show have their own id.

I don't want to have to use finds for this purpose. I also want to be able to assign each call the crew from a field that contains crew codes separated by space.

Also, I want to be able to if a crew member is not found, the option to delete the unfound member or characters or create a new Id for the name

Edited June 2, 2015 by yilbber
Needed to add on a question/request.

[eos]

How do I set it so that i can create a Check list for crew from the pool of crew already assigned for a show?

​Basically …

• use an x relationship to all Projects
• use that for a selector portal
• use a script to select a project and set its ID into a (presumably global) field
• use that field for a relationship into the ProjectCrew join table
• use that relationship for a selector portal
• use a script to select/deselect (select all/none) from that list, storing the selection list in a $$var (or a field, or a global field)
• use a script with a loop to assign selected crew to the new project, i.e. create join table records for each selected crewID with current projectID

In the portals, use Conditional Formatting to give visual feedback re current selections.

No idea how large the crew for any project can be, but you could probably use a portal filter to apply additional criteria like availability etc.

Questions? 

I also want to be able to assign each call the crew from a field that contains crew codes separated by space.

​This may be relevant, but I've no idea what it might mean. You should assume that nobody here knows your industry and its terminology and processes (Call?).

And just to be sure: you should use (auto-entered, meaningless) IDs to uniquely identify records and implement your relationships; what data you display is another matter.

Also, I want to be able to if a crew member is not found, the option to delete the unfound member or characters or create a new Id for the name

​Whatever you mean by that (character? not found?), it is probably another argument (if you really need one) for using meaningless, artificial IDs, rather than natural keys (which are ultimately beyond you control).

[yilbber]

 Ok, already have half of that setup, .. as for CC, in our case it is just initials/Acronym to identify a person, ie, JS for john smith.

A field would have 1-4 names, as well as +x (number) crew which would be unassigned.I would also like for it to have - ignore apostrophes, parenthesis,  and if an unknown name pop up, the option to either add a new record or delete the entry.

 

 

[eos]

I suggest you start from the beginning, show us your data model, and describe your intended workflow – because I have no idea what you mean.

[JILLW]

I'm thinking that you need a many to many relationship.  That is three tables

Crew    Projects and Calls

The Calls table contains the id's of the crew member called and the project for which he/she is called.

Read up on many-to-many and Join tables (Calls is a join table)