1 2 Previous Next 16 Replies Latest reply on Jan 18, 2013 1:58 PM by philmodjunk

    Trigger script in drop down list to select a related table in a multiple portal layout

    JohhnyHilly

      Title

      Trigger script in drop down list to select a related table in a multiple portal layout

      Post

           I have a layout with multiple portals, each portal only being one row. On the left of each portal/row I would like to have a drop down list to select an item, for example "Clutch", which once this is selected, the fields in that portal will be avaiable or linked to Clutch to input information about that "Clutch" item, for example "Total Running Hours" and "Running Hours since last oil change".

           Can this be done? Since there are multiple portals on this layout, does each portal come from a seperate table occurance? Can someone please let me know how I can go about doing this? Thanks in advance.

        • 1. Re: Trigger script in drop down list to select a related table in a multiple portal layout
          philmodjunk

               It's impossible to fully answer your question without a more complete description of your database. What you describe to this point could be done with a single portal--It's not immediately obvious why you would have multiple portals here and how one portal would be different from another.

          • 2. Re: Trigger script in drop down list to select a related table in a multiple portal layout
            JohhnyHilly

                 It's a little long to explain but will do my best to be concise.

                 I have a vessel. On that vessel are numerous pieces of equipment. These equipments vary between different vessels. So on one there might be Clutch and Gearbox, on another Clutch and Bow Thruster, just as an example. This means from vessel to vessel the pieces of equipment are never exactly the same hence the need to be able to choose different pieces of equipment in each of the single row portals (as the entire set of portals on the one layout would represent a single vessel). From my understanding, a single portal would not be able to accomodate this type of information input.

                 I have attached a screenshot of what I thought the single row portals would look like. As for the setup of the database I am thinking of having a table that is linked/related to a seperate table for each of the pieces of equipment. I have attached a screenshot, where the main table "MDR" is linked to "MDR Data" via the Bow Thruster and "MDR Data 2" via the Port Clutch. This would be repeated for each piece of equipment (which can number up to about 15).

                 So from the layout, the 2 single row portals are coming from "MDR Data" and "MDR Data 2" respectively. This is why I would like a drop down list on the left side to select a piece of equipment and then have the fields on that single row portal be related to the appropriate table.

                 I hope this makes sense. I am not sure if this is the most efficient way of doing this hence why I am asking these questions.

            • 3. Re: Trigger script in drop down list to select a related table in a multiple portal layout
              philmodjunk
                   

                        From my understanding, a single portal would not be able to accomodate this type of information input.

                   Why would that be? I don't see any portal on your screen shot, BTW.

                   I would recommned this table structure:

                   Vessel-----<Equipment_LIst>-------Equipment

                   Vessel::__pkVesselID = Equipment_List::_fkVesselID
                   Equipment::__pkEquipmentID = Equipement_List::_fkEquipmentID

                   See the first post in this link if my notation is unfamiliar:Common Forum Relationship and Field Notations Explained

                   With this setup, Each record in Equipment would document a specific type of equipment. (A clutch of a particular make and model for example would be one record.) Each record in Equipment_List would identify a specific type of equipment installed on a specific vessel. Data common to all equipment of a specific make and model would be stored in fields in Equipment while data specific to a particular piece of equipment on a particular vessel would be stored in fields in Equipment_List.

              • 4. Re: Trigger script in drop down list to select a related table in a multiple portal layout
                JohhnyHilly

                     There are two portals in that screenshot. Each of those two lines is a single row portal.

                     Anyway, I setup the table structure as you recommended and have setup the Equipment layout with a field creating a record for each piece of equipment. How do I setup the Equipment_List layout to assign numerious pieces of equipment to a specific vessel. I'm guessing then that once a specific vessel has been assigned all of it's equipment, I go back to the original portal (in the screenshot) and link that vessel to it and all those equipments should appear in each of the rows of the portal? 

                     This is what I am understanding from your description above. Am I correct in saying this?

                • 5. Re: Trigger script in drop down list to select a related table in a multiple portal layout
                  philmodjunk
                       

                            How do I setup the Equipment_List layout to assign numerious pieces of equipment to a specific vessel...

                       Ever fill out an invoice in FileMaker? It's much the same only you are selecting items of equipment instead of items to purchase.

                       Set up a layout based on vessel with a portal to Equipment_List. Put the _fkEquipmentID field in this portal and set it up with a drop down list of EquipmentID's for the first (Primary) field and a name or description field as the secondary field. Enable "allow creation of records via this relationship for Equipment_List in the relationship between vessel and Equipment_List.

                       Now you can build an equipment list by selecting equipment from the drop down list in the rows of this portal.

                  • 6. Re: Trigger script in drop down list to select a related table in a multiple portal layout
                    JohhnyHilly

                         Phil, this is a little off topic of this post but relevant to this DB. I have setup a layout to print, linked to Equipment_List. On this layout I also want to have the details of "Company, Contact Name, Address" etc from the Contacts table through merge fields but I can't seem to get it to work. What relationships do I need to have between Contacts, Vessels and Equipment_List for them to come up in the printed layout?

                    • 7. Re: Trigger script in drop down list to select a related table in a multiple portal layout
                      philmodjunk

                           If you have relationships like these:

                           Contacts-----<vessels-----<EquipmentList

                           Contacts::__pkContactID = Vessels::_fkContactID
                           Vessels::__pkVesselID = EquipementList::_fkVesselID

                           Then you can place any fields that you need from Contacts on your EquipmentList based layout. Often, such fields are placed in the header of such a layout. You do need to perform a find or use Go To Related Records to pull up the correct EquipmentList records for a given vessel (or all the vessels for a given contact.

                           This assumes only one contact record for a given vessel. If you have multiple contacts--say several part owners of a single vessel, then you need a join table between contacts and vessels and this will create a problem for your equipment list as you will need a way to select a specific contact to show on your EquipmentList layout or add a portal set to slide up in a leading grand summary part to list all of them.

                      • 8. Re: Trigger script in drop down list to select a related table in a multiple portal layout
                        JohhnyHilly

                             Phil, I built the Equipment_List layout with the portal successfully, however, I am having the same issue as I did from this post here (http://forums.filemaker.com/posts/95e16e5262) where I am selecting a vessel on the Equipment_List layout to assign the record to but then it is changing the vessel under the Vessel layout. So exactly the same problem as in that other post. The only difference is that in the other post, the portal is being used to bring up information that has already been inputted. With this Equipment_List layout, I am entering information into this portal manually. Not sure if this makes a difference in how to overcome this problem.

                             Do I need to do exactly the same thing to overcome the issue? i.e. duplicate the Equipment_List, name it Equipment_ListBygVesselName, use the global field gVesselName etc? Or is there another way that I should assign a Vessel to the equipment list?

                              

                        • 9. Re: Trigger script in drop down list to select a related table in a multiple portal layout
                          philmodjunk

                               The answer to that will depend on how you design your user interface. If your user interface design matches that of the other post, then likely, the same solution will work. But it's not the only option. I would consider adding a "new vessel" button to your layout that creates a new Vessel record and then change the drop down to work from a global field with a script that finds existing vessel records rather than just updating the portal. That assumes that there is also data from the vessel record that you want to see on the layout in addition to the equipment list data shown in the portal.

                          • 10. Re: Trigger script in drop down list to select a related table in a multiple portal layout
                            JohhnyHilly

                                 Is there a way I can do what you suggested without having to create a new vessel record each time? The thing is with this layout/portal, once the record is created and assigned a vessel, I will be continuing to manually update the portal in this layout. What I am trying to say is, I won't be needing to create another record for the same vessel.

                                 I want to avoid creating a new vessel in this Equipment_List layout as I already have a Vessel layout for the purpose of creating a new vessel record.

                            • 11. Re: Trigger script in drop down list to select a related table in a multiple portal layout
                              philmodjunk

                                   The button would not be used each time--only when you need to start filling in information on a new vessel. If you have a different layout to use for that purpose, then disregard my suggestion about adding a "new vessel" button. The key detail is making the field with the drop down a global field and performing a find to pull up a vessel's record.

                              • 12. Re: Trigger script in drop down list to select a related table in a multiple portal layout
                                JohhnyHilly

                                     Ok. That's all done but an error is coming up saying "No records match this find criteria" and not too sure why as I have 2 vessel records at present. Help?

                                     Another question, how does the find work as I have a layout based on the Vessel table but the portal in that layout is based on Equipment_List?

                                • 13. Re: Trigger script in drop down list to select a related table in a multiple portal layout
                                  philmodjunk

                                       You are finding vessel records which then link by Vessel ID to the equipment list for that vessel.

                                       You may find it helpful to examine this thread on scripted finds: Scripted Find Examples

                                       You may find that you need to post the script that you are attempting to use:

                                       To post a script to the forum:

                                         
                                  1.           You can upload a screen shot of your script by using the Upload an Image controls located just below Post A Answer.
                                  2.      
                                  3.           You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here.
                                  4.      
                                  5.           If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
                                  6.      
                                  7.           If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format. (Use the HTML option on the database tab panel and paste the text into the forum's HTML editor.)
                                  • 14. Re: Trigger script in drop down list to select a related table in a multiple portal layout
                                    JohhnyHilly

                                         Phil, after reading through your thread on scripted finds the problem is solved. Much appreciated.

                                    1 2 Previous Next