13 Replies Latest reply on Feb 13, 2017 2:24 AM by thehalpeen

    Create a list from a selection in a Found set

    thehalpeen

      I have a list or found set of contacts. I 'select/mark' a few of these via checkboxes - see screenshot below.

       

      Can I create a 'return delimited list' in a field, of these 'marked/selected' contacts - so I can use that field in a multi-key relationship.  I hope that makes sense.

       

      Contacts Checkboxes.PNG

        • 1. Re: Create a list from a selection in a Found set
          beverly

          See Phil's answer (and download the sample file!), specifically the "Checkboxes with scrollbars". While you may not be looking for scrollbar, the demo does make a "multi-line key" (How they work).

          beverly

          • 2. Re: Create a list from a selection in a Found set
            thehalpeen

            Beverly,

             

            That is a very useful file. However, the example you gave me is based on portals. A portal won't work in my scenario.

             

            I'm trying to select from the 'parent table' and produce a list from the records I select.

            • 3. Re: Create a list from a selection in a Found set
              beverly

              it's the same principle. What you "check" gets pushed into a global field (or removed with "uncheck"). Then a Condition actually is what shows the checkbox, based on the relationship. Check how it's done...

               

              The point is that if you are having many users, then checking and unchecking of values (records) makes the other person's list wrong. Using the global makes it user session based (each person uniquely per open file).

               

              The "list" is really what has been pushed to the global field. Is that what you need?

              beverly

              • 4. Re: Create a list from a selection in a Found set
                thehalpeen

                Beverly,

                 

                Thanks - that looks like the answer. The person who created this file has gone to a lot of trouble. It seems I need to trigger a script everytime I click a checkbox. I guessed that would be the case, but this confirms it.

                 

                Thanks again.

                • 5. Re: Create a list from a selection in a Found set
                  philmodjunk

                  Thank you for the kind words.

                   

                  The method can also be used to create/delete records in a join table. This can then enable a typical many to many relationship--which can offer the same relationship matching as a multi-value key but now offers other features--such as reports based on the join table not readily possible with a multi-value key approach.

                   

                  I've actually been thinking lately of adding an update to this file to demonstrate this option.

                  • 6. Re: Create a list from a selection in a Found set
                    thehalpeen

                    Beverly,

                     

                    Who is Phil? Filemaker are indebted to him. Plus people like yourself and several others - Mike Beagrie, Wim DeCorte and others who seem to be very generous with their advice.

                    • 7. Re: Create a list from a selection in a Found set
                      thehalpeen

                      Phil,

                       

                      I've just written a script that implements the techniques you used to create a list from checking items in a portal - I used a set of records instead of a portal and it works beautifully.

                       

                      I hope I don't embarrass you on this forum, but I am in 'awe' of your scripting and technical ability.

                      • 8. Re: Create a list from a selection in a Found set
                        beverly

                        "hal", you have learned the secret! Sharing has always been a part of the FM community. We learn from each other. Typically I'll add a, "Tag you're it" here, so as you learn something, you share it with the next person who asks!

                        We don't always remember where we learn something, passing along is the key. And many come up with the same answer almost like magic, at the same time. I remembered Phil's answer to another post and forwarded the link.

                        "TAG!"

                        beverly

                        Sent from miPhone

                        • 9. Re: Create a list from a selection in a Found set
                          philmodjunk

                          One of the things that I enjoy while participating in this forum is seeing someone that I've helped start helping others.

                          • 10. Re: Create a list from a selection in a Found set
                            thehalpeen

                            Phil,

                             

                            Apologies, but I'm having trouble with my script.

                             

                            I have a list of records.

                             

                            When I click a checkbox called ‘select’ it sets that field with a ‘1’ and populates a field called ‘SelectedContacts’ with the category of business (Charity, Commercial, Domestic, Retail).  The script is based on a script developed by PhiModJunk. See script below.

                            Set List.PNG

                            This works nicely.

                             

                            My problem is when I deselect a record – I want to remove the category of business from the ‘SelectedContacts’ – but only if there are no other contact in that category.

                             

                            Example – if I click the ‘charity’ record and then the 4 ‘Commercial’ records – and then proceed to deselect all the ‘Commercial’ records, the ‘Commercial’  category stays in the ‘SelectContact’ field - the 'yellow' field.

                            list of contacts.PNG

                             

                            I’ve tried using if statements to count the number of selected records in a category – see below and then some sort of formula that will remove the ‘Type’ category from the ‘SelectContact’ list – but I’m having no luck.

                             

                            Execute SQL.PNG

                             

                            Is it possible to do this – to remove the type category from the list if some records from a category are selected and then deselected?

                             

                            I have attached the file for reference.

                            • 11. Re: Create a list from a selection in a Found set
                              philmodjunk

                              You have an unusual approach here. appatently clicking a contact does not select the contact, but instead selects the contact's TYPE, not the contact, correct? Or maybe both?

                               

                              If so, you need two lists in two fields. Add a second field that lists the selected contacts so that you have one that lists contact IDs and one that lists contact types. You can then use the list of IDs to match to contact ID in a self join relationship.

                               

                              You can then use filtervalues on a list of contact IDs from that related table occurrence to check to see if that's the last contact of a given type selected.

                               

                              But it instead of maintaining a field listing contact types, you could just define a calculation that lists the contact types of the selected contacts. Formatting it as a group of check boxes will hide the duplicates.

                              • 12. Re: Create a list from a selection in a Found set
                                thehalpeen

                                Phil,

                                 

                                Apologies again - I should have explained what I am trying to achieve.

                                 

                                I have 2 tables

                                Contacts

                                Shifts

                                 

                                shift list.PNG

                                contacts.PNG

                                 

                                When i select contact, I only want the associated shifts for those contact to appear in the popup window. If I select contact from Housekeeping - I only want to see those shift, etc.

                                 

                                Below is the relationship on which that popover list is based - selectedContacts to Department

                                relationship.PNG

                                 

                                 

                                Everything works, until I deselect contacts. - if I select 2 contacts from Admin and then 4 from Housekeeping, and then deselect all the Houskeeping contacts - the 'Housekeeping' remains in the 'Selected Contacts' list - even if I've deselected it. - and therefore the associated shifts for 'Housekeeping' appear in the popup menu.

                                 

                                I hope I'm making sense!

                                 

                                I have attached a revised file to show what I'm at.

                                • 13. Re: Create a list from a selection in a Found set
                                  philmodjunk

                                  I don't see how that changes what I put in my last post. You need to track both the ID's in one list and the types in another. There are several ways to use the list of IDs in a relationship to manage the list of types.

                                   

                                  Please note that I'm using my phone to respond and thus cannot look at your file at this time.

                                  • 14. Re: Create a list from a selection in a Found set
                                    thehalpeen

                                    Phil,

                                     

                                    Great - based on your advice, I worked out a solution. I wasn't able to figure out exactly what you were advising me to do - with contact ID's and Type ID's and lists and filterValues - so I'm not certain if my solution is as elegant as yours.

                                     

                                    Reminder: My objective was to produce a list of shifts based on the selected contacts - when a contact is selected -only related shifts should appear in the 'select shift' popover

                                     

                                    I created three fields to do this:

                                    1. This enters the employees 'department/category' in the cSelectedContacts field when a contact is selected

                                    select contact equals 1.PNG

                                    2. The field below summarises the above field using the List summary

                                    list summary.PNG

                                    3. Finally, because I could not use the above field in a relationship graph, I hadto create the field below, based on the field at 2 above.

                                    list calc.PNG

                                    How would yours differ based on your advice below?

                                    If so, you need two lists in two fields. Add a second field that lists the selected contacts so that you have one that lists contact IDs and one that lists contact types. You can then use the list of IDs to match to contact ID in a self join relationship.

                                     

                                    You can then use filtervalues on a list of contact IDs from that related table occurrence to check to see if that's the last contact of a given type selected.

                                     

                                    But it instead of maintaining a field listing contact types, you could just define a calculation that lists the contact types of the selected contacts. Formatting it as a group of check boxes will hide the duplicates.

                                     

                                    I have attached the new file with the working solution.