11 Replies Latest reply on Feb 14, 2013 3:45 AM by NaturSalus

    Dynamic Picker?

    NaturSalus

      Title

      Dynamic Picker?

      Post

           Hello,

           The first time that I tried to create a database with FM I faced the following challenge: how to use the same data with different purposes on the same layout..

           So imagine that you have a layout based on table B, and on this layout you need to add names of people from table A. The caveat is that you could need to use the same name several times on the same layout, of course for different purposes.

           So imagine that John Smith filled in a customer complaint, and John Smith carried out the investigation of the customer complaint, and John Smith proposed a remedial o corrective action, and John Smith...

           This is the second question that I asked in this forum and Phil showed me the way to do it: for each task use a different table occurrence of table A.

           A few months later I realized the shortcomings of FM value list when you are dealing with lengthy lists. Again, Phil came to the rescue with his apporach for pickers illustrated in the enhancendvaluelist demo. 

           I have been using Phil's approach to picker setting up since then.

           The problem is that when you have to create 25 + table occurrences for table A and their corresponding pickers the task at hand is quite tiem consuming. And one can't stop thinking: "there must be a more effficent, less labor intensive way to do it"

           So here it goes my question:

           Is there any way to create just one table occurrence and picker for table A and use it "dynamically"?

           Is there a way to reuse table occurrences and relationships established without having to create as many of them as times the same data (name) could be used in a layout to table b?

           Thanks,

           natursalus

            

            

        • 1. Re: Dynamic Picker?
          philmodjunk

               Keeping the number of Table Occurrences to a minimum is why Filemaker 12 introduces the ExecuteSQL function. It can often greatly reduce the number of TO's actually needed. Haven't tried it for this type of "picker" but it may be worth a try--though my early test with that idea reveal that you can't use a scroll bar to scroll through multiple matches like you can a "picker" portal.

               But in this generalized context, I'm not clear on why you need so many different occurrences of the same table just to select values in the first place...

          • 2. Re: Dynamic Picker?
            NaturSalus

                 Hello Phil,

                 Thanks for looking into my question.

            But in this generalized context, I'm not clear on why you need so many different occurrences of the same table just to select values in the first place...

            Maybe the link to the original question might be of help:

                  

            Iterative data on the same layout

                 Thanks, naturslaus

            • 3. Re: Dynamic Picker?
              philmodjunk

                   Consider that same demo file.

                   There are three portals across the bottom of the screen. All three portals reference the same table occurrence, but if their filter expressions are different, different sub sets of the total set of related records can be different and thus each portal can display a different set of records. Since each portal has it's own set of buttons, a different script (or the same script with different data passed to it as a script parameter) can be performed to enter the selected record's ID into different fields and/or records in your database or even do a completly different task with the selected record.

                   And yet there is only one table occurrence used for all the "pickers"...

              • 4. Re: Dynamic Picker?
                NaturSalus

                     Hello Phil,

                      

                There are three portals across the bottom of the screen. All three portals reference the same table occurrence, but if their filter expressions are different, different sub sets of the total set of related records can be different and thus each portal can display a different set of records. Since each portal has it's own set of buttons, a different script (or the same script with different data passed to it as a script parameter) can be performed to enter the selected record's ID into different fields and/or records in your database or even do a completly different task with the selected record.

                And yet there is only one table occurrence used for all the "pickers"...

                      

                     It's true but as you mention  "if their filter expressions are different" 

                     And I guess that for the same setting the way to do it is by passing different data as a script parameter, as you mention. But I am not sure how to do it.

                     My current setting is the following:

                      

                Tables-----------------------------TOs

                     Report----------------------------Report

                     Person---------------------------report_reportPerson_PERSON;  Selection_PersonForReport

                     ReportPerson------------------report_ReportPERSON

                      

                Relationships

                     Report::__kp_Report  =  report_reportPERSON::_kf_Report

                      report_reportPERSON::_kf_Person =  report_reportPerson_PERSON::__kp_Person

                     Report::gSearchPerson X  Selection_PersonForReport::PersonForReportRefresh

                      

                     Being Selection_PersonForReport::PersonForReportRefresh = Report::gSearchPerson

                      

                Layouts

                     Report_Detail

                     Report_New

                     PersonOfReport_Selection

                      

                Layout Settings

                     The purpose of the Report_Detail layout is to record what has been done and by who.

                     In this case it makes no sense to display tasks and people in just one portal. This is the reason why a portal to the report_ReportPERSON TO is placed wherever the name of a person carrying out a task is needed.

                      

                     Report 1

                     ......

                     Originator: John Smith

                     ......

                     Controlled by: John Smith

                     ......

                     Investigation Approved by: John Smith

                     .....

                      

                     On the Report_Detail layout there is a portal to the report_ReportPERSON TO with two fields:

                       
                •           Person::NameLastFirst
                •      
                •           Person::JobTitle

                     There is an Add button to the right side of the portal that triggers the Launch PersonOfReport Selection script.

                     As mentioned before:

                       
                •           The pircker purpose is to selecte from a lenghty list of people.
                •      
                •           The problem is that on many ocassions, the same person (Person record) carries out dfferent tasks on the same Report (Report record). 

                      

                     On the PersonOfReport_Selection layout there is your picker with the following objects:

                       
                •           New button that triggers the New Person script
                •      
                •           Cancel button that triggers the Cancel Selection script.
                •      
                •           Report::gSearchPerson field that OnObjectModify triggers the PersonForReportUpdateSearch script
                •      
                •           Portal to Selection_PersonForReport TO and with the following filter:

                     IsEmpty (Report::gSearchPerson)  or PatternCount (Selection_PersonForReport::NameLastFirst ;Report::gSearchPerson)

                     The Portal Selection_PersonForReport TO has the following two fields: 

                       
                •           Selection_PersonForReport::NamLastFirst
                •      
                •           Selection_PersonForReport::JobTitle

                     Finally, these two fields are grouped and bth trigger the PersonOfReport_SelectionPortal script based on your equivalent script.

                      

                Picker Scripts

                PersonForReportUpdateSearch script:

                     Set Field [Report::gSearchPerson; [Report::gSearchPerson]

                      

                PersonOfDeviation_Selection Portal script:

                      

                     Set Variable [ $SelectedPersonID; Value:Selection_PersonForDeviation::__kp_Person ]
                     Freeze Window
                     Go to Layout [ “DeviationPerson” (DeviationPerson) ]
                     New Record/Request
                     Set Field [ DeviationPerson::_kf_Person; $SelectedPersonID ]
                     Set Field [ DeviationPerson::_kf_Deviation; $$DeviationID ]
                     Go to Layout [ “dev_Deviation” (Deviation) ]
                     Freeze Window
                     Adjust Window
                     [ Resize to Fit ]
                     Close Window [ Current Window ]
                     Halt Script

                      

                     I really don't see how this can be accomplished or waht is the best way of doing it.

                      

                     Outside the database concept the best way of doing it would be to create the fields needed in the Report table:

                       
                •           Report::Originator
                •      
                •           Report::Registrar
                •      
                •           Report::InvestigationRequestedBy
                •      
                •           Report::Investigator
                •      
                •           Report::InvestigationApprovedBy
                •      
                •           ...

                      

                     So the user just enters the name of the person that carried out each task. In this way the real name of the person that did someting is recorded in a specific field.

                      

                     Under the database concept, we assign to each record the ID's of their related records. So, to the right of  the Report Originator label we add the ID of the related Person record, although we show a name.

                      

                     Sometimes it doesn't make much sense to me this convoluted way of carrying out easy tasks.

                      

                     Anyhow, could I pass script parameters to the Picker scripts?

                      

                     Thanks,

                     natursalus

                      

                      

                      

                      

                      

                      

                      

                • 5. Re: Dynamic Picker?
                  philmodjunk

                       I would use a global field or variable to record the current "context" when the user clicks a button to open the Picker. THen the script performed when you select a person from the picker can check this value and perform completely different steps if needed, to enter the selected ID number into the correct field.

                       Example:

                       A button next to the "originator" field passes "originator" as a script parameter. The script that takes the user to the picker layout sets $$Target to that text. The button next to the "controlled by" field passes "controlled by" as a script parameter.

                       Then your select person script has this general structure:

                       If [$$Target = "Originator"]

                          #Do the steps needed to assign the selected person to the originator field

                       Else IF [$$Target = "Controlled by"]

                          #Do the steps needed to assign the selected person to the "controlled by" field.

                       Else If [//and so forth...]

                       End If

                       This is the most generally applicable method. In some cases, you may simply need to pass the TableOccurrenceName::FieldName of the target field to your script for use with Set Field By Name instead of set field.

                  • 6. Re: Dynamic Picker?
                    NaturSalus

                         Hello Phil,

                         Thanks for the feedback, but I don't understand the rationale behind it.

                         As I said before, and based on what said on this link: Iterative data on the same layout

                         My understanding was that the only way to assign several times the related John Smith person record to the Report1 report record was by using different portals to different TOs of the ReportPerson join table.

                         Report TO --< ReportPerson TO >-- Person TO

                         So if through a portal to a ReportPerson TO I have chosen the record John Smith and associated to the Report 1 record. Everytime that I need to associate the same John Smith record to the Report 1 record, I have to use another portal to a different TO of the ReportPerson join table.

                         If this is true, then I don't understand how can I associate the same Person record several times to a Report record using just one Table Occurrence of the ReportPerson join table.

                          

                    • 7. Re: Dynamic Picker?
                      philmodjunk

                           That's not what I am talking about. I am referring to the table occurrence of the portal fro which you are selecting a person.

                           We have two different issues here--the number of table occurrences needed to associate different people to the same report record and the number of "picker" table occurrences needed in order for the user to make those selections.

                           So far, we have only discussed the latter.

                      • 8. Re: Dynamic Picker?
                        NaturSalus

                        So far, we have only discussed the latter.

                             I was getting really confused.

                             Point 1. My main concern is whether I can reduce the number of table occurrences needed to associate different people to the same report record because this is the foundation on which the pickers are developed.

                        Ponit 2. Concerning the pickers, my understanding was that each picker is developed upon point 1. That's a picker setup for each table occurrence of Point 1.

                        Thanks for your patience

                        • 9. Re: Dynamic Picker?
                          philmodjunk

                               Point 2 is what we have been discussing. This is not the case and I have been providing examples of how to use one such table occurrence for selecting values in multiple contexts.

                               Point 1 isn't something you can do much with. One way or another, you need multiple relationships and in FileMaker 12, there are only two basic ways to do that: Multiple table occurrences that all refer to the same table or, in some cases, ExecuteSQL calculations can access data from relationships only defined within the SQL expression created and evaluated within that function.

                          • 10. Re: Dynamic Picker?
                            philmodjunk

                                 But do consider this option:

                                 Reports----<SelectedPersons>-----Personnel

                                 Reports::__pkReportID = SelectedPersons::_fkReportID
                                 Personnel::__pkPersonnelID = SelectedPersons::_fkPersonnelID

                                 Add a "role" field to SelectedPersons that holds "orginator", "controlled by", ... etc

                                 Then a filtered portal to a single occurrence of SelectedPersons can list just the Originator by filtering out all related records except the one where Role = "originator". Put a second copy of the portal on your layout and change the filter expression and you get the "controlled by" or other person linked to that report...

                                 This works to display data from a related table, but the related table is not accessible for calculaitons.

                                 An alternative to the filtered portal, is to use an ExecuteSQL calculation that uses "Originator" in the WHERE clause to access the correct data. This result is accessible for use in calculations.

                            • 11. Re: Dynamic Picker?
                              NaturSalus

                                   Hello Phil,

                                   Thank you for your insightful suggestions.

                                   FM Pro12 Advanced v3 still buggy. So I have to deal without the ExecuteSQL capability.

                                   Minor scripting based on your last suggestion was enough to achive what I wanted, and all this with just one TO and its associated picker.

                                   natursalus