9 Replies Latest reply on Jan 15, 2015 6:34 AM by jbrown

    How to look up value from a field in another table if record contains a certain value in another field

    schpa

      Hey!

       

      So I'm trying to make a calculated value that automatic fills a field in a layout "Orders" from a field in a related table "Partners" if the record is marked as the standard partner.

       

       

       

      The way I've selected if the actual partner is the standard partner for the related firm is by a field called "Standard" with two values: "Yes" or "No"
      Only one partner per firm will be marked as "Standardpartner" for the selected firm and this is the partnerinfo I want automatic to be filled in as the reference partner for the order, but that later on can be changed if desired, therefore making the field an automatic look-ed up value field that can be changed by the user.

       

      Now, I've tried all kinds of different calculations that decides that it will import the partner data IF the partner is marked as "Yes" under the "Standard" field in their respective records located in the "Partner" table, if this makes sense.


      One of the calculations looks like this:
      ( If ( Partner::Standard = "Yes" ; Partner::Name ; "" ) & Get ( ActiveFieldContents )

       

       

       

      This obviously does not work as intented as it either will not import anything, or just import the first related partner data to the firm selected in the order layout.

       

      Any ideas to on how to make this work? I've tried searching all over for a similar request but I couldn't find it =/

       

      Thanks in advance!

       

      Simen

        • 1. Re: How to look up value from a field in another table if record contains a certain value in another field
          jbrown

          Good morning.

          There seem to be a few ways to do it.

          If I'm clear, you want to go to the partner table and get the name of the person that is the marked "Yes" as the standard partner.

           

          A simple way to do this is to create a relationship between the orders table and the partner table. In the orders table you'd have two fields: an ID_Firm field (which gets filled in during the order, I assume?) and a Yes_ct field. This field would be a calculation set to "Yes". That means every record would have "Yes" in this field.

           

          The relationship keys you'd use would be Order::ID_Firm = Partner::ID_Firm    and Order::Yes_ct = Partner::Standard

           

          What this will do is find only the partner of the firm on the order that has a "yes" in that Partner::Standard field.

           

          Then you can use auto-enter calculation to populate the name field, via the relationship. OR you can write a script to put the name in the Order::ParnterName field. Set this to a trigger when the firm's ID is entered. I prefer the latter (the script).

          Hope that helps.

          • 2. Re: How to look up value from a field in another table if record contains a certain value in another field
            wimdecorte

            You may not want to make this a calculation...

             

            If the standard partner changes at a company, all of the old Orders will change. Usually you don't want that, you want to be able to see who the standard partner was at the time of the order.

            • 3. Re: How to look up value from a field in another table if record contains a certain value in another field
              erolst

              If there's only one “standard” partner per company, then that's an attribute of the company, and you should store the partner's ID in a foreign key field in the Companies table.

               

              Then create a relationship between company::id_standardPartner = Contacts::id.

               

              And 1+ to what Wim said; this is an information you should store with each order; since you know the related company of the order, you can simply look up the standard partner ID when specifying the companyID, via the Order-Company relationship.

              • 4. Re: How to look up value from a field in another table if record contains a certain value in another field
                schpa

                Alright, so I basically tried the last option that you purposed, namely performing a script upon editing the Company ID field in the orders layout, grabbing the partnernumber from the related record containing the value "Yes" in the Partners::Standard field using a perform find script and it looks like this:

                 

                Go to Layout: ["Partners" (Partners)]

                Set Variable [$yes; Value "Yes"]
                Go to Related Record [From Table: "Orders"; Using layout: <Current Layout>]
                Enter Find Mode []

                Set Field |Partners:Standard; $yes]
                Perform Find []

                Go to Field [Partners::PartnerNr]

                Set Variable [$partnr; Value: Get( ActiveFieldContents )]

                Go to Layout ["Orders" (Orders)]
                Enter Browse Mode []
                Set Field [Orders::PartnerNr; $partnr]

                 

                This however grabs the partnernumber and sets it into the partnernumber field in the orders layout, but not the one containing the word "Yes" in the standard partner field, but the first one listed in that particular company, which isn't always the standard partner.

                 

                Any ideas on what I've done wrong?
                • 5. Re: How to look up value from a field in another table if record contains a certain value in another field
                  schpa

                  This is not too bad of an idea really, however I'd like to avoid creating as many relationships as possible since it quickly becomes a mess if there's too many relationships going on, so I'd first like to try that script option grabbing the ID of the partner with "Yes" checked in the Standard field.

                   

                  But yea, you're both right about the partner not changing at the specific orders if the standard partner of the company changes, it is preferable to avoid that.

                  • 6. Re: How to look up value from a field in another table if record contains a certain value in another field
                    jbrown

                    Do you use at all ExecuteSQL?
                    You could simply say ExecuteSQL("
                    SELECT Name FROM Partners WHERE ID_Company = ? and StandardPartner = ?"; "";""; ID_Company ; "Yes")

                     

                    Or in your method, you are on the Order layout. The Company ID has been filled in:  Therefore, you'd:

                    1. Go To Related Record into partners table (if you have a relationship between Order & partners via Company ID), using any partners layout, showing matching records only.
                      1. This would take you to all partners of that company (the company's ID you filled in)
                    2. Enter Find Mode
                    3. Set Field StandardPartner = "Yes"
                    4. Constrain Found set.
                    5. Get Partner ID into a variable ($ID)
                      1. here all you have to do is do "Set Variable  [$ID value =Partners::ID_Partner]. You don't have to go to that field and then get(ActiveFieldContents)
                    6. Go back to the order layout
                    7. Set the ID_Partner field with the value of the variable.

                    But the relationship method that I described will ensure that FileMaker only sees those partners who belong to that company and have a "yes" in the StandardPartner field. It is a whole lot simpler to reach across that relationship and get the name value.

                    • 7. Re: How to look up value from a field in another table if record contains a certain value in another field
                      schpa

                      No I haven't ever used ExecuteSQL, I've only been into filemaking for a couple of weeks and have been set as a practician in a small company to help build a new database as they'd like to convert from Access to FileMaker, no earlier experience from Access or anything so yeah, I'm kind of a newbeginner.

                       

                      I tried following the steps you mentioned in the script but it can't find any records containing the word "Yes" for some reason.

                       

                      I think I'll just try the relationship option though I'd really prefer to just grab it from a related record using a script. But, oh well, whatever works.

                      • 8. Re: How to look up value from a field in another table if record contains a certain value in another field
                        erolst

                        Well, you can still use a script if you like, with a Find or ExecuteSQL(); the upshot is that …

                         

                        1. with storing the partnerID in company, you have one field that will be filled with a much greater likelihood than an “isStandardPartner” field in Partners ( every company has a standard partner, but only a single one of any number of partners is a standard partner at any given time).

                         

                        2. when changing the standard partner, you change a field in one record, instead of having to set the current and reset the previous partner.

                         

                        3. when creating a new order, you get the standardPartnerID with a simple lookup / auto-enter.

                        • 9. Re: How to look up value from a field in another table if record contains a certain value in another field
                          jbrown

                          You can still use a script to set the Orders::PartnerID field with a script. It can be done in one line of code, rather than doing many lines of code to go to the related records, and finding the one record with the "Yes" of that company's partners.

                           

                          A relationship is really a self-find. The order table only 'sees' the one partner of that company that is the standard partner. (If the standard partner changes, the order would see the changed partner).

                           

                          You mentioned that your relationship graph can get messy and so you're hesitant to put in more Table Occurrences. If the database is small, another TO won't matter any. Keep them organized and you'll be in good shape. Look at the methods of organizing the relationship graph: Anchor-buoy, and so forth. Those will help you stay organized.

                           

                          Give some thought to erolst 's proposal of storing the partner ID in the company table. That makes sense as well.