8 Replies Latest reply on Aug 25, 2015 9:14 AM by greengk2fi

    Combining If & GetNthRecord in Calculation

    greengk2fi

      I'd like to use a calculation that says

      If the value in payment type is equal to the value on the 1st record in lookup payment type, use PaymentAmount, if not use 0. I

       

      t seems like a simple calculation and I can't find an error in my approach. But filemaker 14 continually returns the error "An operator (e.g. +, -, *, …) is expected here."

       

      Here is the calculation that I think ought to work:

      If (PaymentType = "GetNthRecord (lookupPaymentType::PaymentType"; 1); PaymentAmount; 0)

      Here is the calculation highlighted after the error.

      Screen Shot 2015-08-23 at 7.29.41 PM.png

       

      Based on the error I've changed things around; but never with success.

      I tried

      If (PaymentType = ("GetNthRecord (lookupPaymentType::PaymentType"; 1)); PaymentAmount; 0)

      Oddly I get the error There are more “)” than there are “(” or there are more “(” than there are “)”. I think I can count and I count 3 openers and 3 closers.

      Here is the calculation highlighted after that error:

      Screen Shot 2015-08-23 at 7.34.34 PM.png

       

      Any ideas/help is much appreciated.

        • 1. Re: Combining If & GetNthRecord in Calculation
          BruceRobertson

          Why are you using quotes? They aren't needed in this calc.

          • 2. Re: Combining If & GetNthRecord in Calculation
            stephensexton

            Remove all quotation marks that you have in this example ... it should then work assuming appropriate relationships are in place between the table called tblNVOICE and lookupPaymentType

             

            Regards, Stephen S

            • 3. Re: Combining If & GetNthRecord in Calculation
              greengk2fi

              Thanks, you were both correct about the quotes. I was editing an old script where the evaluation was based on text I had directly entered in the calculation. Sadly it seems as though I misunderstand the purpose of GetNthRecord. I thought that it would return the Nth record from a particular table regardless of relationship. So if I requested the 1st record of an unrelated table, I'd be able to compare my current data to that first record in the other table. Apparently that is not the way it works.

               

              When I corrected my calculation to

              If (PaymentType = GetNthRecord (lookupPaymentType::PaymentType; 1); PaymentAmount; 0)

               

              I got the error "This field comes from an unrelated table.  Only global fields can be referenced in unrelated tables." But if I were accessing a global field, I wouldn't need to GetNth record as simply identifying the field would do the job.

               

              In the past, I simply had text from this second table in a Value list and the text entered into the invoice table. It was sufficient for my needs and I could use the evaluation with directly entered text in the calculation. Now I'm offering my db to a friend and they're changing payment types. So I thought I could get to this data a different way without having to update for the invoice table to record the IDs of the payment type. I don't want to change the calculation every time they add a payment type. I'll do it if I have to; but I'm curious if there is a workaround.

               

              Is there any way of making this work?

              • 4. Re: Combining If & GetNthRecord in Calculation
                BruceRobertson

                Making what work? It looks very much like you're on the wrong track here.

                This sort of thing is generally handled through lookups or scripted set-field operations.

                But it would help a lot if you would present the big picture here.

                What is this all about? What are you trying to do?

                • 5. Re: Combining If & GetNthRecord in Calculation
                  greengk2fi

                  I created a db to manage my business. It's awesome for my needs. One aspect of it is a business overview report that shows how many services of each type were offered, how much money was earned by each service type, etc. In my summary I have $ received also broken down by payment method. It all works fine for me even though I set up the capture of payment type oddly. In my db I have a lookup table with fields as payment ID and payment type description. But for some reason, I captured only the payment description in in my invoice table. This is the only value list in which I'm not recording the ID. Anyway, when I built my summary calculations, I directly entered things like "cash" "check" etc. As I say this calculation works fine for me.

                   

                  However now that I'm offering my solution to a friend I'd like to allow them to update their payment types without me redoing the calculations every time. She has begun using the solution and I thought that if I had a comparison like the equation I started, I could make a simple updates to her file. Then whenever she changed payment types, the comparison would work to gather certain payment types.

                   

                  I can go back in and set the invoice table to capture ID rather than description and have the appropriate relationship. However, that means updating everything she's already done record by record. Thus my search for a useful comparison in non-related tables.

                  • 6. Re: Combining If & GetNthRecord in Calculation
                    BruceRobertson

                    Get the design right. Forget getNth for this purpose, way too fragile and inflexible.

                    • 7. Re: Combining If & GetNthRecord in Calculation
                      Magnus Fransson

                      Hi greengk2fi,

                       

                      Let me start by saying that my FileMaker version is in Swedish, so any calculation “cut-and-pasted” would be useless to anyone not using a Swedish FileMaker, so I won’t do that.

                       

                      That said…

                      I think you can solve the problem using a combination of ExecuteSQL(), to get a table with the unrelated values, and GetValue(), to select row in that list of values.

                       

                      Best regards Magnus Fransson.

                      • 8. Re: Combining If & GetNthRecord in Calculation
                        greengk2fi

                        thanks for the feedback.  I updated the design yesterday and will just work on scripting to take care of the old records. However I'm still going to look into your suggestion just to learn a little bit more about how that function works. Again thanks to you both