CODEWITHSUNDEEP
databasefilemaker
Emma
Emma

Reputation: 65

Filemaker Pro add entry for each selected record in another table

I have two tables that are linked with a common field. I am trying to set up a script or layout or whatever might work to achieve the following. I need to be able to select certain records from table 1 and then have the program give me a view that will allow me to add an entry to table 2 for each of the selected records in table 1. Table 2 has multiple fields that I want to complete just once for all of the selected records. I am presuming that I need to set up a script with a loop but if someone could get me started with the basics, I would greatly appreciate it. Or if there is a way to do it with a layout, that would be great too! Using Filemaker Pro 12.

Upvotes: 1

Views: 3219

Answers (1)

michael.hor257k
michael.hor257k

Reputation: 116993

If I understand correctly, you are describing a many-to-many relationship between Cattle and Events: each animal can have multiple related events; and an event can relate to more than one animal.

If this is correct, you will need a third join table to resolve the many-to-many relationship into two one-to-many relationships:

Cattle -< CattleEvents >- Events

The way this works, you enter the event's data once into the Event's table, then proceed to associate different animals with the event, by creating a join record in the CattleEvents table for each. The join table needs to contain (at least) fields for CattleID and EventID.

Unlike other RDBMSs, Filemaker also allows you to create a many-to-many relationship without a join table; you can define a CattleID field in the Events table as text and format it as a checkbox using a value list of CattleIDs. However, this "shortcut" has many shortcomings when it comes to summarizing and reporting. Another problem with this method is that you cannot record anything specific about the join: for example, with a join table you can not only record an event of vaccination affecting 46 selected animals; you can also record the individual dosage each animal received, by entering it into a field in the join table.

ADDED:

Once you have the desired found set in the Cattle table, move to the Events table, create a new record, then have a script do the following:

Set Variable [ $eventID ; Events::EventID ]
#
Go to Layout [ Cattle ]
Go to Record [ First ]
Loop
  Set Variable [ $cattleIDs ; List ( $cattleIDS ; Cattle::CattleID ) ]
  Go to Record [ Next ; Exit after last ]
End Loop
#
Go to Layout [ CattleEvents ]
Loop
  Set Variable [ $i ; $i + 1 ]
  Exit Loop If [ $i > ValueCount ( $cattleIDs ) ]
  New Record
  Set Field [ CattleEvents::EventID ; $eventID ]
  Set Field [ CattleEvents::CattleID ; GetValue ( $cattleIDs ; $i ) ]
End Loop
#
Go to Layout [ original layout ]

At the end of the script, you should find yourself back in the Events table, with all the cattle from the found set in the Cattle table appearing in the portal to CattleEvents (assuming you have placed such portal on the layout of Events). In that portal, you may add the specific data for each animal related to the event.

Upvotes: 1

Related Questions