Accessing a single line item on an invoice
I'm trying to pull the TOTAL value of a single line item in an invoice that has 3 line items consisting of £195, £56, and £18.
After trying everything I can think of, I keep getting the totals for all the line items instead of just 1 i.e 195,56,18 appearing in my formula field.
Can anyone show a typical code example to do this (if it is possible)? Many thanks.
16 replies
-
Hi
How are you deciding on which line you want to see the total for.
Regards John
-
I am stuck in there as well. working on that for a week, no clue
-
Roger It would be helpful if you can provide the code you are using and a bit of background to the tables and fields you are using.
-
Roger You can use the item function to access a single line. Arrays are zero indexed so you need to subtract 1 from the number you use, so
let a := 2 item(LineItem,a-1).LineTotalWill give the line total for the second line
Regards John
-
John Halls
Thanks for your reply.
Actually, I'm a newbie for Ninox, my code might be odd or quite complicated, still learning.
I found a similar code to pull the data out. I put a switch to aim which one is activated for use.
In my case I've got the 'Summary View' in the 'Customers' table that shows every customer's Status and details, also I got the "Total Due Payment" field to show the Total of the due payment in an individual Invoice which is under the same Name.
Main Table:
New Customers (like a bowl, contains everyone who has been touched)
Name
Tel
E-mail
…
Customers
Full Name (linked to New Customers.Name)
Invoice Date
Start Date
End Date
Total Due Payment (I'd like to show £138 = £60 + £78, not £223 = £60 + £85 + £78)
Due Date
Invoice (View field)
Invoice Number Invoice Date Subtotal Status
Invoice 1 12/03/2022 £60.00 Activated
Invoice 2 12/04/2022 £85.00 Cancelled
Invoice 3 25/04/2022 £78.00 Activated
...
Invoices (every change happened, I'll create a new one, the old one will archiving)
Customers (linked to Customers.Name)
Invoice Date
Start Date (pull the date from Customers table, but if changed date, will trigger to update back to the upper table)
End Date (pull the date from Customers table, but if changed date, will trigger to update back to the upper table)
Subtotal (SUM ‘Invoice Item’.Cost)
Status (Activated/Cancelled)
…
(Subtable)
Invoice Item
Item Description Qty Unit Price Cost
Item1 Item1 10 £2.00 £20.00
Item2 Item2 8 £2.00 £16.00
Item3 Item3 12 £2.00 £24.00
Code like down below:
let id := Id;
let name := (select Customers where 'Id' = id).'New Customers'.'Full Name';
let status := 1;
sum((select Invoice where Customers.'Full Name' = name and Status = status).Subtotal)Maybe made some mistake when I type in, caused I translate the field name from my original language.
Currently, it worked, but when in another table, especially going deep, or multilayer, I'll be mashed up.
Keep working on it. Thanks for everyone.
-
John Halls Would you possible make an example to explain how it works. What is a, and how have they organised line item mach to a? I'm a little bit confused.
-
Roger in the code
let a := 2 item(LineItem,a-1).LineTotala is a variable to hold the line number. It's not really necessary.
LineItem is the array of line item records. Item pick just one of them and a-1 is the one.
LineTotal is the field we are looking for.
Regards John
-
John Halls Working on a bit of time, finally, it works. Thank you so much!
Roger
-
-
In my case, is pulling the data from the Invoice. Also, I did try to pull the line item info from each invoice. You might try to aim at the item name I guess, or any unique info you are able to aim at.
My one look like this
let id := Id;
let type := 1; // The unique type I could aim at (I got 1-4 different type) //
let name := (select Payment where Id = id).'Name'.'Customers'.'Full Name';
"" + (select Payment where Type = type and Name.Customers.'Full Name' = name).AmountHope it could help
-
Can you post the code from the new table?
Can you let us know the relationship between the new table and Payments?
-
Roger said:
Fred It worked currently in the Summary table, but when I try to pull data in another table, it shows nothing. I'm a newbie in writing code, I thought maybe I confused and mashed up the data location and relationship in different tables, still working on it, hopefully, can solve it out.Sorry for not responding for so long. How are you doing with this topic?
Roger said:
let id := Id; let type := 1; // The unique type I could aim at (I got 1-4 different type) // let name := (select Payment where Id = id).'Name'.'Customers'.'Full Name'; "" + (select Payment where Type = type and Name.Customers.'Full Name' = name).AmountLooking at the above code, it looks like you are trying to show the data from the Amount field in the Payment table. An important thing to remember is that Ninox always returns an array when doing a select. Even if Ninox returns only 1 record.
So your last line:
(select Payment where Type = type and Name.Customers.'Full Name' = name).Amount
will not return anything because Ninox does not know what to do with the results of the select Payments. You need to tell Ninox to do an array function like first or last or concat. If you are only expecting one record you can use the first command:
first((select Payment where Type = type and Name.Customers.'Full Name' = name).Amount)
This will return data from the field Amount.
-
Fred Yes! That was the most headache thing for me. Ninox only gives the first, the last or concate, but most time I need the data which are one or few in an array. Finally, I used John's idea, and solve it, even still complicated. but I have got what I want.
Code like this
let id := 'Invoice Number';
item((select Invoice where 'Invoice Number' = id).'Invoice Details'.Cost, 3)That is for pulling the 4th line item's cost
Also, still needs 'Invoice Details'.Items / 'Invoice Details'.Description and so on.
At least, it works for me now
Thanks for all.
Roger
-
-
You can try this:
let id := 'Invoice Number'; let xRec := item((select Invoice where 'Invoice Number' = id), 3) let xCost := xRec.'Invoice Detail'.Cost let xItems := xRec.'Invoice Detail'.Items let xDesc := xRe.'Invoice Detail'.DescriptionWhat this does is at line 2 instead of only getting the data from the field Cost. We are putting the entire record into a variable. Now we can call whatever field is in that record.
Another example would be at the beginning. You could replace line 1 with:
let t := this; let xRec := item((select Invoice where 'Invoice Number' = t.id), 3) let xCost := xRec.'Invoice Detail'.Cost let xItems := xRec.'Invoice Detail'.Items let xDesc := xRe.'Invoice Detail'.DescriptionThe this command pulls all the fields of the record you are on. Then you can later specify which field you want to use. In this case it is probably overkill to use this. But if you need to search on three or four different criterias then doing one line of this would be easier than four separate variables.
Content aside
- 1 yr agoLast active
- 16Replies
- 401Views
-
3
Following