9 Replies Latest reply on Oct 22, 2015 8:01 AM by Mike_Mitchell

    Verify Values to another Table


      I have an inventory manager that keeps track of product Id , location, and bin assignment. Basically the user should be able to change which Bin the product is in, without a drop down it needs to be a text field.


      I am trying to fix an error where if the user adds a bin that is not existent in our Bins table which the bin assignment above points to, it shows a popup, or something of the sort that asks them are they sure they want to save, in which case it would add a new record to the bins table with that value and then save it, or it would allow them to cancel the save


      Thanks for your help

        • 1. Re: Verify Values to another Table

          Since you mentioned that there are a fixed number of bins, but you don't want to use a value list, I'm going to assume there is a large number of bins, or the bins change a lot. Therefore, I'm going to suggest you do this with a Script Trigger (since we're talking about a user assistance protocol rather than something for data integrity per se). Here's a general outline of what I would probably look at:


          1) Create a Value List of all the bin ID numbers that exist. (This allows FileMaker to keep an indexed list, which is much faster than doing it on the fly.)


          2) Create a script that compares the value in the bin assignment field against the available bins. Use something like:


               IsEmpty ( FilterValues ( binAssignment ; ValueListItems ( "" ; valueListName )))


          If this calculation is true, it will tell you that the value the user has inserted doesn't already exist.


          3) Attach the script to the OnObjectSave Script Trigger for the field. Have it do whatever you like - a Custom Dialog, spawn a popover, whatever - that then allows him to create a new bin record, cancel the entry, etc.


          As a caution, this sort of thing is dangerous. Users can wind up populating your tables with all sorts of false junk because they typed something wrong. When you allow editing of domain tables (something that defines the "domain" of allowable values), it's a good idea to restrict it to a user role given only to authorized users who know what they're doing. Otherwise, you will wind up with a data integrity problem.





          • 2. Re: Verify Values to another Table

            Hi Mike thanks for the explanation,


            On Number 2, where should I put that script, also I have a value list called "Bins", does this go to valueListName?



            • 3. Re: Verify Values to another Table

              Not sure I understand the first question. Scripts are created in the Script Workspace:




              Once it's created, you choose the field where the user is making the update. Right-click and select Set Script Triggers:




              Choose OnObjectSave:




              As for the second question, yes, the name of the value list is inserted where I have "valueListName".

              • 4. Re: Verify Values to another Table

                I dont see IsEmpty or FilterValues as a script step

                • 5. Re: Verify Values to another Table

                  They aren't. They're calculation functions. Your script should look something like this:


                  If [ IsEmpty ( FilterValues ( binAssignment ; ValueListItems ( "" ; valueListName ))) ]

                       Show Custom Dialog [ "Error" ; "Bin does not exist. Do you want to create a new one?" ; "Yes" ; "No" ]

                       If [ Get ( LastMessageChoice ) = 1 ]

                            Go to Layout [ Bins ]

                            New Record / Request


                            Exit Script [ False ]

                       End If

                  End If


                  Now, this is VERY rough. It will largely depend on what you want the script to do when the user puts in a bad value. And again, this is not necessarily a good idea for data integrity reasons.

                  • 6. Re: Verify Values to another Table

                    IsEmpty(FilterValues(BinDetail;ValueListItems ( "" ; BinNames )


                    Is this what I am supposed to do,


                    I have a Bins Table that I have used to make the value list, this keeps track of Id's of Bins

                    Tha Value List is called BinNames


                    The table the user edits all the bin names in is called BinDetail, the field is BinName


                    The error I am getting is the specified table cannot be found referring to BinDetail

                    • 7. Re: Verify Values to another Table

                      Also we are using data level security at the database level, The customer wants to type in Bin Names instead of having a dropdown.

                      • 8. Re: Verify Values to another Table

                        You need the name of the field the user is editing. So you need to use BinName where BinDetail is now.

                        • 9. Re: Verify Values to another Table

                          You don't have to use a dropdown if you don't want to. The filter will alert the user if the entry doesn't match the existing entries.