4 Replies Latest reply on Aug 23, 2014 3:33 PM by FentonJones

    Search, Portal and a 2nd Table



      Search, Portal and a 2nd Table


           Hi, I have been using FMPro off/on for many years.  I have a decent understanding of related tables and portals.  And I have tried to solve the following on my own, but have not.  So I am appealing to the experts here! :-)  

           I have two tables  (engagements and personnel) linked via a JoinTable.  Each table has a unique key derived from an auto-index #.  When a person in the personnel table and an engagement in the engagements table are associated in the JointTable, the person's name and photo appear in a portal in the engagement layout. 

           I can manually edit the jointable to have any person in the personnel table appear in the Engagement's layout portal.  The idea is to know which employees were involved with each engagement.  Each engagement is unique, but individual employees can be involved in many engagements over time.

           I have a button on the Engagement Layout, near the portal, called "EDIT" and when clicked, this button fetches an alternate layout of the Personnel Table only citing photo, name and title along with a check box to the far left.  The idea is to have a user in the Engagement Layout click the button and simply select from the Personnel database who should /should not be appearing in that specific Engagements list of personnel (employees).

           I am doing this via script but it's not going well.  It's the only aspect of this database I have not been able to solve.   Regarding the relationship between the engagement table, the personnel table and the joint table, I have enabled the engagement table to create/delete records in the joint table. But it's getting the selection mechanism correctly connected to allow dynamic selection of personnel from the personnel table and have that update the joint table in order to appear in the engagement's layout portal of personnel.  That's the challange. 

           Any ideas/suggestions would be GREATLY appreciated. 

        • 1. Re: Search, Portal and a 2nd Table


               First, re: "clicked, this button fetches an alternate layout of the Personnel Table".  The word "fetches" doesn't tell us whether you "went to a layout of the other table (table occurrence), or whether you opened a new window. 
               Either would work however, if you used the Script Step 'Set Variable'. If you go from one table's (table occurrence's) layout to another then you need to "save" the ID you want to use, before you go back somewhere else (different table's layout) to use the value in a Set Field step. 
               [ You may not need to "save" the value if both the table occurrences are connected with a relationship that could see the correct field's value already; but in your case, it does not when you are creating the "join" record.]
               So you use either Set Variable or use a global field to "save" the ID, before the script continues. [ I prefer a $variable_name, as it only exists while that script is running.]
               If we could see your script we'd know better whether this is the problem. The easiest way to show us (a short script) would be to save it as a picture, which you can attach to a post.
               P.S. I am no longer an "expert" as I have trouble with both reading and writing. But heck, many people have some trouble with these also :-/
          • 2. Re: Search, Portal and a 2nd Table

                 Thank you for your quick reply.  

                 Ok, let me clarify please.  When I say "fetch" I mean an alternate layout for Personnel appears in a new window from the Personnel table, listing a check box, headshot, name and title of all personnel in the table.  

                 Attached is a screenshot of the table relationships.  

                 The idea is this: a user is in the Engagements Layout.  They want to identify the persons who actually worked on this engagement (project). So, they click "Edit" next to the portal of photos/names and the new window/alternate layout for Personnel appears. The user can scroll through the list and check the box of the person they are wanting to add to the engagement portal list of employees.  It works, but does not populate the portal because:

                 1. The personnel list is not connected to the joint table (not sure how to do that); I can present the list and find the selected and close the window.  That's all.

                 2. I don't know how to manipulate the JointTable of engagements and personnel, which determines the employees who will be cited in the engagement portal. Hopefully I am describing this clearly and it makes sense to you.

                 I need to have a script that searches the JoinTable for a specific EngagementID (global variable set while in Engagement Layout),  as to delete it or create a new entry in the JointTable once the "edit" button is clicked in the Engagement layout.  I don't know how to search the Jointable's entries once the user clicks "edit".

                 The ideal steps would be:

                 1. User clicks the "edit" button (works)

                 2. A list of personnel appears; the user can scroll through the list and select via check box the personnel who should be cited in the engagement's personnel portal

                 3. Upon "OK" the script searches the jointable for the engagement's ID, deletes all of the engagement's occurrences in the jointable (reset for this engagement)

                 4. Then the scrip, using the engagement's ID and the individual IDs from the selected personnel, creates new record for each selected personnel in the jointable citing both the same engagement ID and the individual ID.  

                 5. The portal window is then updated with the personnel's photo, name, and title. 

                 Hopefully I am better describing what it is I have done and where I am trying to go.  Thanks for your assistance.






            • 3. Re: Search, Portal and a 2nd Table

                   Attached is the script (I know it's waaaaaay incorrect, but you get the idea of my logic).


              • 4. Re: Search, Portal and a 2nd Table


                     Here's my ideas. You could use either global fields (text) or global variables ($$variable_name). [ Or you could use local (script only) variables ($variable name); but in case you'd have to pass it/them to any sub-script which needed them (using the "Optional script parameter" when you call the sub-script, and getting it back with "Get ( ScriptParameter )". ]


                      Lets use a global variable, i.e., $$variable_name.


                     A. Set Variable [ $$eng_id; Engagement_ID ], before leaving the layout


                     Go to Layout "personnel"


                     B. Set Variable [ $$person; Personnel_ID ], after person clicks to continue

                     1. You'd want to check that they did, or give them an option to "cancel"


                     C. You're saying the "one 1 person can have the Engagement"? (you say it was "unique")

                     1. In which case your first sub-script goes to the "join" table's layout, does a Find for the engagement, using $$eng_id as the value to find.

                     a. Delete if any found (there should be only 1 at most, right?)


                     D. Create "join" record, using the two variables above to set the two ID fields.


                     E. Set Variable [$$eng_id;""] and Set Variable [$$person;""], just to clean up.



                     P.S. Don't test the above on live data :-|