Why are you using quotes? They aren't needed in this calc.
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
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?
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?
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.
Get the design right. Forget getNth for this purpose, way too fragile and inflexible.
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.
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.
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