11 Replies Latest reply on Feb 15, 2017 1:43 PM by siplus

    Copy 1 or more records from a portal to be linked to a record in a different table

    xxx202xxx

      Hi All.  You guys have been a great help in getting my system up and running. My next question will require detailed help (scripting).

       

      Problem:  I want to have a check box for each record in a portal where I can either check 1 box to select a record or check multiple boxes to select multiple records.  Once that is done I would like to create a "submit" button to add the record/s to an existing trouble ticket where the two tables are related.

        • 1. Re: Copy 1 or more records from a portal to be linked to a record in a different table
          xxx202xxx

          Because of my lack of knowledge here are the tables relationships:

           

          Asset Table is related to my Service Table

          Service Layout has a portal from the asset table

          The fields in my portal are:

               asset::ID, asset::IP, asset::hardwaremake and asset;;hardwaremodel

          The fields on my service table;

               service::ticketID, service::network, service::assetID_fk, service::status - just to name a few

          • 2. Re: Copy 1 or more records from a portal to be linked to a record in a different table
            philmodjunk

            Explain "trouble ticket". Is that a record in a third table? You stated that it already exists when you click submit. How would a script find the correct "trouble ticket"?

            • 3. Re: Copy 1 or more records from a portal to be linked to a record in a different table
              xxx202xxx

              Sorry, It's a record in the Service table.  service::ticketID

               

               

              When a ticket is first opened (service layout) it is assigned a assetID_fk along with other asset fields.  The portal will then show all records related to the asset based off of IPs. (same range of IPs)

               

              Im not sure about how to write the script to allow the  'submit button' to point to an existing ticket with the certain records in the portal checked.

              • 4. Re: Copy 1 or more records from a portal to be linked to a record in a different table
                keywords

                From what you describe, I assume:

                1.     Assets table lists equipment that needs routine servicing from time to time, as well as repair as needed

                2.     Service table lists servicing sessions, and that servicing might involve more than one asset—e.g. a service session might include washing and polishing all the widgets, or repairing a broken leg on one of the quartpots.

                If the above is correct, then you have a many to many situation and will need a Service line items table to link the two main tables.

                To then set up what you describe—selecting particular assets to be marked for a service ticket—is quite straightforward. On a Service Ticket layout you could do one of the following:

                1.     Create a portal listing all the assets on LHS of screen, and a line items portal elsewhere on the layout. Then create a script activated on the list portal row which grabs the assetID, goes to the line item portal and posts the assetID into a new record.

                2.     Have just the line item portal on the layout with the asset foreign key field in that portal set up with a value list that shows all the assets. Choosing an asset from the list will create the line item record which will automatically be linked to the current service ticket.

                • 5. Re: Copy 1 or more records from a portal to be linked to a record in a different table
                  xxx202xxx

                  I have the relationship set up with a join already.  My issue is getting a check box in the portal where when I pick an asset "all" of the assets are not selected but just the assets I want selected.  Then after selecting my asset or assets create a submit button to then append these assets to the existing ticket.

                  • 6. Re: Copy 1 or more records from a portal to be linked to a record in a different table
                    philmodjunk

                    But if you are on the service layout and looking at a portal of related assets, they are already linked to that service (trouble ticket) record. It seems like you want to "keep" the selected records and omit the ones not selected. Don't really see a need for a submit button, the needed changes can take pace with each mouse click on an asset.

                     

                    You may need to share more about the relationship used for that portal. Is it a Cartesian join to list all assets or some such?

                    • 7. Re: Copy 1 or more records from a portal to be linked to a record in a different table
                      xxx202xxx

                      I modified my solution a bit.  I'm now on my ticket/asset join table.  The portal only show a limited amount of IPs based off of the first 3 octets.  The IPs are related to the asset because of similar IPs but they are not apart of the ticket.  That's why I need to be able to select an asset by IP to add to the ticket if need be.  My issue here is still getting the checkbox to function independently instead of click one box then all the boxes are then checked.

                      • 8. Re: Copy 1 or more records from a portal to be linked to a record in a different table
                        xxx202xxx

                        Hot Damn, I'm cooking with Gas.  Ok, so I got the check boxes to behave as I wish.  But now the really hard part for me.  I need help scripting how to then add these assets to my ticket now that I am on my join table.

                        • 9. Re: Copy 1 or more records from a portal to be linked to a record in a different table
                          siplus

                          the way I deal with this is the following (no checkboxes):

                           

                          - on layout enter, i clear $$ChoiceList

                           

                          - when you click on a portal row, I add the portal line's pk to $$ChoiceList if not there,

                             otherwise I eliminate it from $$ChoiceList

                           

                          - your action is reflected by a conditional formatting which highlights the row

                             if its pk is in $$ChoiceList (and does not if not)

                           

                          - I have a select all and a select none buttons which clear $$ChoiceList or set it to list (pk)

                           

                          - when you click submit I pass $$ChoiceList to a script which knows its job.

                          • 10. Re: Copy 1 or more records from a portal to be linked to a record in a different table
                            philmodjunk

                            I'm now on my ticket/asset join table.  ...

                            That really doesn't make sense.

                             

                            To assign an asset to a ticket means creating a new record in the join table with both asset and service record ID's. That means creating new records in the layout that is currently your layout's context.

                            My issue here is still getting the checkbox to function independently instead of click one box then all the boxes are then checked.

                            Sounds like the check box field is from a table other than the asset table if this is still a portal to assets.

                             

                            What you need is a button in place of that check box field. It can be made to look and act like a check box, but it's really a button. Siplus has described one method, but you can do this more simply and eliminate any need for a "submit" button.

                             

                            Clicking a button in the portal row can perform a script with logic similar to what he describes but works like this:

                             

                            Check to see if a related record in the join table with that asset and service ticket ID exists. If so, asset has already been selected, delete it. If not, add a new record with the needed asset and service ticket IDs. Since this action immediately adds the needed link (or removes it) with each click, there is nothing to submit.

                             

                            Once you have the button working, there are ways to use Hide object when to show a "tick mark" graphic when a corresponding join table record exists and which is hidden when such is not--thus simulating a check box field.

                             

                            Ironically, I am currently working on an update to my Adventures in FileMaking #2 file that is shared throughout this forum to include an example of exactly this technique.

                            • 11. Re: Copy 1 or more records from a portal to be linked to a record in a different table
                              siplus

                              in my book a selection / deselection should have a minimum impact on the whole system,

                              surely not creating / deleting records anywhere.

                               

                              Only once the client is over with the selecting ritual I act upon data.

                              Definitely and definitively.

                               

                              Thus the need of a submit button, but let's not forget about a maybe more useful "cancel" button, which allows to not even enter the problem.

                               

                              Why should I bother deleting records because the user cancelled ?

                               

                              But that's me.

                               

                              My "cancel" button just closes the popover, my "submit" (labelled "OK") button does the same but continues: it acts accordingly on the $$ not being empty.