12 Replies Latest reply on Apr 17, 2014 6:34 PM by isamudysan

    a secnario and your calculation suggestions


      hello everyone! . greatly appreaciate your thoughts and calculation suggestions in this scenario of mine.


      in my FMP file, my main table, i have an S/N field, the unit type, vendor, and module ID (in my main table):



      Unit Type

      Module ID


      the S/N is always a 17 alphnumeric S/N: ABC123456789TEST1K.


      here's the breakdown:

      • the "TEST1" of said S/N indicates the unit type, the vendor, and the module ID. in a separate table called "family," i have 4 fields respectively named (of course, the first field is the unique ID): indicator ID, unit type, vendor, and module ID.



      Indicator IDUnit TypeVendorModule ID
      TEST1Type 1Vendor 1mID-01
      TEST2Type 2Vendor 1mID-02
      TEST3Type 3Vendor 2mID-03
      TEST4Type 4Vendor 3mID-04
      TEST5Type 5Vendor 4



      • i need to come up with a calculation and/or script that will lookup the "TEST1" part of the S/N in the "family" table and auto-enter the necessary info in their respective fields in the main table.


      as for the relationship graph, not sure if a primary key and foreign key is useful in this scenario (please correct me if i am mistakened) so i related the field "Indicator ID" of the "family" table to "Unit Type" of my main table.


      i've been messing with Middle(text;start;numberOfCharacters) and Lookup but not sure if that's even a good place to begin to try and come up with some sort of calculation and/or script. i think a script trigger of OnObjectModify on the S/N field is probably better? i'm still very new to FMP and still learning it's nuances in particular it's scripting, and calculations (two areas that i want to be consitently good at).


      thx in advance, and i greatfully appreciate all your assistance.

        • 1. Re: a secnario and your calculation suggestions

          I think what you need to do is create a relationship between the main table and the family table using all three fields (Unit Type, Vendor and Module ID) as the key.


          To do this, go to the relationship graph and edit your existing relationship - you will probably have the MAIN table on the left and the FAMILY table on the right. The MAIN table has "Unit Type" highlighted and the FAMILY table has "Indicator ID" highlighted - change that to be "Unit Type" on both sides, then click the "Change" button. Now you're ready to add the next set of fields - select "Vendor" in each list and then click the "Add" button (you'll see another row below the list boxes with the new predicate displayed). Then select "Module ID" in each list and click the "Add" button again.


          At the bottom of the dialog you'll see options for creating new records, deleting related records and sorting records. You want to allow records in the FAMILY table to be created, so check the top box under that table name and then click OK.


          Click OK to close the Manage Database dialog box to save the changes.



          Now you're ready to display the related FAMILY records on a layout based on your MAIN table. Go into layout mode and draw a portal on the layout - a wizard will then appear and guide you through selecting a relationship, displaying fields, etc. When you're happy with the portal, save the layout and go into Browse mode.


          Click on a field in the first empty row of the portal - you'll pretty much only get something useful if you type in the Indicator ID field. FileMaker will automatically create the related record and store the new value there. You will notice that FileMaker has automatically entered values in the Unit Type, Vendor and Module ID fields; it does this to make sure the relationship evaluates to something that will be displayed in the portal (if it didn't, as soon as you exited the first field, the portal row would "disappear" and you would not be able to view the related record).



          This is a bit rushed; if you need further explanation please ask.





          • 2. Re: a secnario and your calculation suggestions

            thank you for replying, and i appreciate it very much


            hmmm...portal does sound pretty interesting. hadn't thought of it. i wonder if i can use a script trigger in the S/N field, something like, e.g., set field [ main table::Unit Type; Middle(main table::S/N ; 12 ;4) ] to auto-enter the info in the Unit Type field of the portal. i'm gonna have to give this a shot when i'm back at work tomorrow as all the necessary info is there, LOL. i'll let you know how it's going.


            thanks again.

            • 3. Re: a secnario and your calculation suggestions

              Ah, OK, I misunderstood. You want a different setup, one that uses lookups:


              Create a text field, gIndicatorID, in the MAIN table. Turn it into a global using the "Options..." button - navigate to the Storage tab. Make a relationship between MAIN and FAMILY where MAIN::gIndicatorID = FAMILY::Indicator ID (no need to create related records on either side).

              Back in the MAIN table, set the Unit Type field options to lookup data from FAMILY::Unit Type (it's in the Auto-Enter tab of the dialog). For Vendor, set the lookup to be FAMILY::Vendor, and so on. Save the changes.


              Now, on your MAIN layout, add a field object for the gIndicatorID. In Browse mode, type a valid value into this field; as soon as you exit the object the other fields will have their values set automatically.



              NB: I have assumed that you don't want to keep the Indicator ID value in the MAIN table, so I specified a global field (one that keeps the same value no matter which record is active). If that's incorrect, turn off global storage.

              • 4. Re: a secnario and your calculation suggestions

                this is getting better and better. however, is it necessary to have the gIndicatorID field in the Main table? i mean, will it be possible to have it work in the background seamlessly with a script trigger to auto-enter the indicator ID from serial number field to the gIndicatorID field? this is exciting! gonna have to give it a shot (since i have a test FMP file at home lol).

                • 5. Re: a secnario and your calculation suggestions

                  Wow, I really didn't read your original post properly, did I?


                  Rather than a global, make the Indicator ID field in the MAIN table be a calculated field with a text result (even after all these years I sometimes forget to change the result from the default type of number).


                  The calculation you want is:


                  Middle ( S/N ; 13 ; 5 )


                  This translates to "Starting with the 13th character in the 'S/N' field, get five characters" which will give you the Indicator ID.


                  Note that your S/N field name is likely to cause errors with the calculation engine since it includes the division symbol - renaming it to "SerialNum" is going to fix that issue.


                  Hope this helps,



                  • 6. Re: a secnario and your calculation suggestions

                    If you want to do it with a script trigger, you would have the script take the same formula as above and store the result in a variable, then switch to a layout based on the FAMILY table occurrence, enter find mode, set the Indicator ID field to the value stored in the variable, perform find, set a variable for each of the Unit Type, Vendor and Module ID field values, go to the original layout and set field for each of the MAIN table fields to the appropriate variable.


                    Given the error checking you would need to add to this process, I think you'll find that the lookups solution is the easier and faster method.

                    • 7. Re: a secnario and your calculation suggestions

                      hmmm....i guess my sample serial number is not 17 characters, lol. so, lets see....


                      set the relationship as such:



                      Main TableRelationship DirectionFamily Table
                      Indicator ID< ------ >Indicator ID
                      Unit Type< ------ >Unit Type
                      Vendor< ------ >Vendor
                      Module ID< ------ >Module ID


                      set the Indicator ID field, in the Main Table, as calculation -- Middle ( SerialNum ; 12 ; 4 ) -- for a 17 alphanumeric serial number


                      and then, set field options for Unit Type, Vendor, and Module ID in the Main Table to look up data from their respective fields in the Family Table.


                      lol, yeah, that script trigger does not sound appealing after reading your last post LOL

                      • 8. Re: a secnario and your calculation suggestions

                        Depending on the version of FM that you are using, you could avoid the relationship graph entirely and use ExecuteSQL to retrieve the data from family.

                        • 9. Re: a secnario and your calculation suggestions

                          i have FMP13 trial at home, while at work we're using FMP12 Advanced.  you know, i've been thinking about executesql, just really don't know where to begine, LOL.

                          • 10. Re: a secnario and your calculation suggestions
                            Stephen Huston

                            Checkout the SQL Explorer tool from Seedcode at:




                            It's a useful file to help one start using SQL in FileMaker, and it's FREE!

                            • 11. Re: a secnario and your calculation suggestions

                              thank you stephen, i have downloaded it and will check it out either tonight or tomorrow

                              • 12. Re: a secnario and your calculation suggestions

                                well, this worked out for me greatly; and, i appreciate yours and everyone's collective replies. i set Indicator ID as a calculation using Middle(text;start;numberOfCharacters). as for the fields of Unit Type, Vendor, and Module ID, i also set them as calculations by using the Lookup feature. somehow, it wouldn't lookup by using the "Options" as you have stated above daniel. in the relationships graph, i have the Indicator ID going from my Main Table to the Indicator ID of the Family Table. gonna be running tests on this solution for a while to make sure that it's supposed to do what it was designed to do, LOL.


                                again, my many thanks.