Combining records...

[ravens2010]

Greetings.

I have a main table for collecting data (event records), which has relationships built to 4 iterations of a data table (Model Records). Certain Models may appear in the event record table as Model 1, Model 2, Model 3, or Model 4 as they are assigned to a given event. I would like to be able to report on each models hours served, regardless of the position they held (1,2,3,4). Is there a script i can generate in a field that will combine any positioning in that event record table.

Edited November 11, 2010 by Guest
The Topic: <a href="http://fmforums.com/forum/showtopic.php?tid/217679">Combining records...</a> was moved from <a href="http://fmforums.com/forum/showforum.php?fid/35">Custom Functions</a> to <a href="http://fmforums.com/forum/showforum.php?fid/32">Rela

[bruceR]

Unclear. Post your files or a simplified example please.

[comment]

If I understand correctly that an event may be associated with more than one model, then you should use another table to join events and models, instead of multiple fields and relationships - see:

http://www.fmforums.com/forum/showpost.php?post/246136/

[ravens2010]

sorry if not clear. i have been killing myself with this project...

Event has model 1 (girl a) model 2 (girl employed.

i have an event record table with all pertinent info about the event, including fields for model1, model2,model3, model4, which allows me to pick the name of the model from a dropdown list which pulls data from a table Model Records.

I would like to report out each model's hourly earnings, regardless of whether they filled the spot of Model1, Model2, etc.

[ravens2010]

here is the relationship tree. you will notice 4 iterations of model records, so i can pull demographic info from each model into the event record. i needed 4 iterations to make sure i could get info only dealing with that model.

bb1relationships.pdf

[comment]

Did you look at the file I linked to? Instead of four "iterations", you should have four (or any number of) records in a join table.

[ravens2010]

will go back and look at how you set that up. trying to figure out how to make that work for this setup. i will also post a pdf of the layout. thanks for looking for me!

[comment]

trying to figure out how to make that work for this setup

Contacts == Models

Organizations == Events

Affiliations == Appearances (Bookings? Employments? Whatever you wish to call it.)

BTW, note the use of serial IDs for relationships instead of names. Names can change, be misspelled etc.

Edited November 11, 2010 by Guest

[ravens2010]

OK...so, eventID has a relationship to venueID and to ModelID, and i can have multiple Model ID's pull out of a table. i like where this is going...thanks! more to come. i will post how i do with it..

[comment]

eventID has a relationship to venueID

Ahm... not exactly. Each event has only one venue (I suppose), so they would be related by a common VenueID (one venue - many events). The event-model relationship is a many-to-many, so you break it up into two one-to-many:

one model - many appearances (in different events);

one event - many appearances (by different models).

[ravens2010]

so, given the example you provided, i have created tables:

Events (primarily where data will be entered)

Models

Supervisors

Venues

Brands

Each table has an IDrecord at connecting to the Events Table and a corresponding ID in that table. My question then deals with entering data. If the supporting tables have data in them, how do i get the data to show on the primary layout? i.e. if i have venue information (address, phone, etc), by choosing the venueID dropdown on the events layout, i want to have those fields poplulate.

am i using the wrong methodology?

thanks in advance...

Jesse

[comment]

If the supporting tables have data in them, how do i get the data to show on the primary layout?

Place fields from the related tables on the Event layout. Make them non-enterable in Browse mode, so users cannot modify them accidentally.

[ravens2010]

oh....and another issue. i need to be able to add two or more models to one event. if i put a dropdown box to choose the first model, no problem. When i add another dropdown to choose another model, the two boxes provide the same values.

[comment]

I am afraid you have missed the point of the join table: the drop-down should be in a portal showing records from the join table. Every model chosen creates a new related record in the join table.

[ravens2010]

ok...that makes more sense. thanks for that clarification. much different situation then. will move on to figuring the portal thing out then.

[ravens2010]

@comment-thanks for your patience with me. I am trying to figure out the terminology with FM as i am converting over from Access. I have set up a new db with the tables i think are required. I have tried to pull apart your demo to understand how to apply it to my solution, and am getting stuck on how the portal interacts with the "join table". If i understand how you set it up, the central table is the "join table" where items from table a and table b reside. the portal then allows you to place items into the join table by picking from either table a or b. is that correct?

i have posted a more simplified set of tables etc to see if my wireframe is correct....

bb2.zip

[comment]

Perhaps this will make it clearer?

bb3.zip

[ravens2010]

@comment-

excellent. so, the portal works well to pull the models down. i like it. i would like to work on the reporting aspect, but that's down the line...clearly you do consulting work. i may be in touch privately regarding rates, etc if this grows more hair than i can manage...thanks!

[ravens2010]

@comment-I have spent some time with the example you posted for me. I understand how it works, i think, and have a question regarding what happens when you choose one or more models in the portal. Where do the records of those selections go? how do i see those in a report? if i choose model id to report on, i am only able to see the one choice, even if there are three assigned to an event. Do i need a script to look for multiple records?

[comment]

What exactly do you want to report on?