13 Replies Latest reply on Jul 20, 2017 6:28 AM by dantrue

    Edit Record List based on Value List

    dantrue

      Hi, I'm brand new to FMP, so I may be trying to do this in a much more complicated than necessary way.

       

      I created a table called FloorTable with two fields, FloorName and FloorFrequency. I used a script to calculate the FloorFrequency of the FloorName in the InventoryTable, which is linked by FloorName. Each Record in FloorTable holds a different FloorName and its corresponding FloorFrequency. Now when users go to input a new Inventory item, they select which floor from a drop-down menu which has values from an editable pre-defined Value List called FloorOptions. When they edit FloorOptions, I want it to update the FloorTable records to make FloorName match, but I cannot reference FloorOptions directly and if I set the drop-down options equal to a field, users cannot edit the list. Ideas?

        • 1. Re: Edit Record List based on Value List
          philmodjunk

          There's a lot in that post that I don't follow, but it appears that:

           

          a) You have a value list of floors and a table of floors.

          b) When someone edits the value list of floors, the changes should then update the table of foors.

           

          Do I understand you correctly?

           

          If so, turn it around. Use the table of floors to generate the list of values in the value list. Edit the data in the table of floors and the value list will automatically update to match.

           

          To do this, Set up a "use values from field" value list that lists values from the floors table. Instead of the "edit" option, add a button next to it that takes the user to a layout or portal for editing the floors table.

           

          A popover with a portal to the floors table with buttons for deleting and adding portal records might serve here, but this is far from the only option and much depends on your table and what you need to do with it.

          • 2. Re: Edit Record List based on Value List
            dantrue

            Sorry for the scattered question.

             

            a) and b) are correct

             

            The only thing with going to a separate Layout is that it will save the Inventory item without the user inputting all the data.

             

            I like the portal approach, but I'm not sure how to implement it. I set up a portal before, but I could only edit existing records. I wasn't able to add or delete records.

            • 3. Re: Edit Record List based on Value List
              philmodjunk

              You might use Go To Related Records to open a window for displaying and editing these records. This will leave your current window untouched and the record uncommitted.

               

              In FileMaker 16, you can also use New Window and specify the new window's layout as a layout for this as another way to do this.

               

              For a portal, there are several options that you can use to add a new record to the portal's table. Some depend on the type of relationship used. If you use a Cartesian relationship with the X operator, you can't enable "allow creation" for the relationship and enter data into a blank row of the portal to add a new record. But this is not your only option.

               

              a) You could go "old school" and use a calculation field with a constant value of 1 as the match field in your layout's record to a number field that has the value 1 for all floor records. Then "allow creation" works.

               

              b) you can add a global field or group of fields for entering data for the new record and then use a save button with the "magicKey" method to create the new record while not leaving the current layout. The new record won't appear in the portal or value list until you commit records, however, so that may cause an issue since you want to keep the current layout record uncommitted.

               

              c) A, open new hidden window, go to layout, create new record, close window technique is often used to add records to a related table. I suspect that this also will have issues with seeing things update until you commit data however.

               

              Ultimately, you may want to rethink how you work with the layout's record such that you avoid the need to keep the layout's current record uncommitted at the time that you do this.

              • 4. Re: Edit Record List based on Value List
                dantrue

                I tried Go To Related Records, but nothing happened when I put in the table name.

                 

                I am in FMP 14, so I'll try the portal methods.

                 

                a) I created a portal that shows related records form FloorTable. I changed the relationship to link via the number field and then I set the portal to Filter by calculation = 1. It only shows the first record's FloorName and doesn't allow for adding or deleting.

                 

                b) and c) yeah, I don't want to save the record

                 

                Is it possible to add or delete a record by Script? If so I could just let the user input a FloorName and then add it, or have them select a floor to delete by a dropdown. I think that is what you were talking about with b) but perhaps there is a way to refresh the value list?

                • 5. Re: Edit Record List based on Value List
                  philmodjunk

                  Go To Related Records requires both that you have a valid working relationship that matches to the records you want to see and that you set up the GTRR script step correctly.

                  • 6. Re: Edit Record List based on Value List
                    philmodjunk

                    It only shows the first record's FloorName and doesn't allow for adding or deleting.

                    Every record in the table has to have a 1 entered into that field if you are going to list all records in the portal. Adding records is made possible by enabling "allow creation of records" for the portal's table occurrence in the relationship set up to make the portal work. To delete records, you need a button for deleting it and may need to enable portal row deleting in portal setup.

                    Is it possible to add or delete a record by Script?

                     

                    This is what I am describing in B and C.

                    • 7. Re: Edit Record List based on Value List
                      dantrue

                      I have entered 1 in each record on the field, and the "allow creation" box is checked on both sides of the relationship. The delete options are also enabled in the relationship as well as in the portal.

                       

                      And so my question for b and c is whether it is possible to reload a value list without reloading the current Layout.

                      • 8. Re: Edit Record List based on Value List
                        philmodjunk

                        I have entered 1 in each record on the field, and the "allow creation" box is checked on both sides of the relationship. The delete options are also enabled in the relationship as well as in the portal.

                         

                        What operator is specified? X or = ?

                        • 10. Re: Edit Record List based on Value List
                          philmodjunk

                          Then it should work. Since it doesn't, compare what you have set up to this simple demo and see if you can spot what's different. Feel free to ask questions about this file if there is some part of it that you don't understand.

                          • 11. Re: Edit Record List based on Value List
                            dantrue

                            OK thank you for being patient.

                             

                            I hadn't set the NumberField itself as a calculation, just the matching criteria. Now I can add new Records, but I don't see a way to delete. I can erase the text in the Portal Field, but that just sets the FloorName to "".

                            • 12. Re: Edit Record List based on Value List
                              philmodjunk

                              click the trash can in my demo. It's a button with the single action: Delete portal row, specified.

                              • 13. Re: Edit Record List based on Value List
                                dantrue

                                Perfect! It all works now. Thank you!