trouble with a simple selection using a multi-choice field
I'm still getting use to the script language syntax and, presently, I can't quite work out a simple selection, as follows:
I have a test sales table. included in this table is a multi choice field (combo list) into which I have manually added vat codes T0, T1, T5,T9. I use this field to select a vat code, once selected I'm trying to lookup the corresponding vat rate% from a referenced VATCodes table and then load this into the SalesVatRate field. I have tried using simple select/where statements and have tried other variations but the script editor keeps reporting errors like 'field not found' etc.
Sales table consists of:
SaleCode, SalePrice, SalesVatCode (choice), SalesVatRate, SaleTotal, Inventory (table reference), VatCodes (table reference)
VATCodes table consists of:
VATCode, VATRate
effectively , I need to select a vat code from the choice field. based on the vat code selection, go lookup this vat code in the VAtCodes table then return the vat rate% and populate the SalesVatRate field
Can someone please help me. thanks
6 replies
-
Hi. I am further updating my query here. I hope that someone can help me sort this out please.
here is my line of code that is giving me a 'field not found' error for the 'SalesVatCode' field
let SalesVatRate := first((select VATCodes)[VATCode = SalesVatCode].VATRate);
this is a trigger after update on the SalesVatCode choice field.
idea: once I select a code in the SalesVatCode field, the code fires and should retrieve the Vat rate% from VatCodes table for the selected vat code.
-
If SalesVatRate is a field then you shouldn't put "let" in front of it. Also, try VATCode = text(SalesVatCode)
-
Thanks , Sean. Yes, of course, SalesVatRate is a field! I have also already tried the 'text(SalesVatCode)' etc but unfortunately I still have the same error.
-
I would like to add a little more here. It is interesting how the 'SalesVatCode' is not recognised in the previous example, and yet, if I modify the line and use this very simple test line:
SalesVatRate := text(SalesVatCode) it is recognised and the line works! So, could there be something at fault in
'[VATCode = SalesVatCode]' ? Is there something wrong with this criteria?
-
EUREKA !!!!
Hi Sean. I think I've solved it now. The lines I have are as follows:-
let VC := text(SalesVatCode);
SalesVatRate := first((select VATCodes)[VATCode = VC].VATRate)it works! .......I understand what I've done here, but I don't understand why I've had to do this work around. Would you be able to explian it ? thanks
-
Mel, I can't say for sure. I am curious whether using "this" would work as well.
let t := this;
SalesVatRate := first((select VATCodes)[VATCode = t.SalesVatCode].VATRate);
If that works then I guess without using "this" or assigning it to a variable like you did, it is ambiguous.
Content aside
- 5 yrs agoLast active
- 6Replies
- 1460Views