1 Reply Latest reply on Dec 27, 2013 7:04 PM by philmodjunk

    Copy Field to Related table ONLY IF not already present



      Copy Field to Related table ONLY IF not already present


           So this is my Layout w/explaination:

           Table A (VALUES) Holds combos of possible values. Setup this way


           Client, Facility, Building, Ward, Room


           Table B (BUILDING_ID) Holds the image of each unique building


           Building_ID (auto Serial), Building_Name, Building_Image


           What I need to accomplish here is to write a script that will automatically create a new record in table B. When a new unique building name has been created in table A.

           This will allow an admin to go back into table B if needed and add a building image for each unique building name. One can assume that the building field in table A will have duplicates as all buildings have multiple wards. 

           The script simply needs to copy the first instance of a new building name to table B, have the auto serial populate, and leave the building container image blank for later modification.

           The plan is to display the building image in a layout via portal. Where if a building name is selected from a conditional value list and the user clicks a button, the button will look up the building name from table B and display its image if it is available.

           Any suggestions, I normally work with Java, and filemaker scripting is throwing me for a real loop! Ive learned a little from PhilModJunk, but Im afraid to say Im still probably in the FMP12 noob category :(


        • 1. Re: Copy Field to Related table ONLY IF not already present

               To me this looks a bit backwards. I suggest the following design:


               TableB::__pkBuildingID = TableA::_fkBuildingID

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               No field for the Building name should exist in TableA. The name field should only exist in TableB where you have just one record for each building. When a record in TableA is added for a Building not yet recorded in TableB, A new record in TableB should be created and it's ID number should be copied to the related record in TableA. On any TableA layouts that need to show the name of the building, the Building name field from TableB should be placed on that layout. (Imagine having to track down multiple records and update their building names when it becomes necessary to change the building name--either because the owner changed the name or because the name was entered incorrectly and then consider that with only one record with the building name, you can make a single change in a single record and all related records in TableA automatically update to show the new name.)

               There are a number of ways to create and link in that new record in TableA, most would use a script similar to this:

               Freeze Window
               Go to Layout ["TableB" (TableB)]
               New Record/Request
               Set Variable [$ID ; TableB::__pkBuildingID ]
               Go to Layout [Original Layout]
               Set Field [TableA::_fkBuildingID ; $ID ]