How to pick up single value from related table
Excuse my ignorance but I am new at this. I have two tables, the first table is called 'Paper Catalogue' and is a list of items and their prices. The second table is called 'Calculation' and has a reference to 'Paper Catalogue'. I want to be able to select an item from the 'Paper Catalogue' table and have a calculation field display the 'Price per 1000' in the 'Calculation' table.
My calculation field curently has this. (select 'Paper Catalogue').'Price per 1000' but this displays an array of all of the records in 'Paper Catalogue'. How do I get it to only show the price related to the previously selected record?
I do hope that makes sense.
7 replies
-
@dyrck,
Check out https://ninoxdb.de/en/manual/calculations/reference-of-functions-and-language
and look for aggregation .. and you will see a
first (......)
Wrap a select in the first .. and you get one and only one row.
-
@Mconneen. I have struggled with the manual to be honest. However wrapping that statement in a first just gives me the first value in that table every time, irrespective of the selection in the other field. I seem to have got what I need with the followong but it may not be the best solution.
let price := 'Paper Type'.'Price per 1000';
price -
@dyrck,
I may not be answering the question asked. Perhaps this will help. The following image shows a table of key / value pairs. It also shows the results of a first statement. When you do a select.. even if only one row is returned.. the select will return an array.. so you either pull off the first row.. or you use the first function to return one and only one object.
So, in this example.. I want the first row that has a "value" of 2.
-
Thank you @Mconneen but I am asking, I think, a simpler question. This is what I'm trying to do. Imagine this scenario. Table 1 is a price list with three items , Nut at $1, Screw at $1.50 and Washers at $2. On table 2 I want to be able to select the item (Screw) and, in a separate field, have the price ($1.50) displayed. I started with a select statement but ended up using the 'let' statement as detailed above. Is this the best way to do this? I really appreciate your help.
-
@dyrck,
Is this thread similar to your situation? : https://ninoxdb.de/en/forum/technical-help-5ab8fe445fe2b42b7dd39ee7/formula-for-a-button-5e2c61c34e9f3771e468dd3e?post=5e2dca76f4fcd768b3ed499d&page=1
Steven
-
@dyrck
Here is a typical tax rate table that I use in various order capture applications.
Then.. on the Order table.. Here is the function to find the appropriate tax rate, based on date.
So perhaps the above will help?
-
@Mconnen
Thanks for your help. I have made much progress.
Content aside
- 4 yrs agoLast active
- 7Replies
- 1844Views