Related table field reference
ok....I know that by the time I will read the answer I will feel very dumb, but...sometimes you have to learn things the hard way
I created a N:1 relationship between TABLE1 and TABLE2 and now in I need to sum all the values of FieldB in TABLE2 only if the values in FieldA of TABLE2 are the same.
Here's my question: since FieldA in TABLE2 is the value coming from TABLE1, how do I distinguish the two of them in a formula or in a script if I need to compare?
Hope the question isn't too messy...
Any fields mentioned after the where clause of a select statement, or in the square brackets come from the table mentioned before the where clause or square brackets. You need to declare a variable to hold the record number of any other tables before they can be used, eg
let a := this; sum(TABLE2[FieldA = a.FieldA]).FieldB
with both formulas (used in both tables) I get this error: "Expression does not return multiple values".
You get this when you try to follow a link in the wrong direction.
It sounds like you created a link to Purchases in Customer not in Purchases to Customer. In Ninox you always create the link in the many to the 1.
You originally wrote:
I created a N:1 relationship between TABLE1 and TABLE2
Which means for every one record in Table2 there are many records in Table1.
If I create the exact scenario then create a formula field in Table1 (the many side):
Ninox is fine with this.
The moment I add the square brackets, because I want to filter the relationship,
Ninox says "Expression does not return multiple values".
This is normal. Since Table2 is the 1 and Table1 is the many there is no need for a filter as there is only 1 record in Table2.
All of this leads me to believe that you have incorrectly created your links between Customer and Purchases. I'm guessing that 1 Customer can have many purchases. So you should create a reference field to Customer in Purchases. In the end, a record in Customer it should show a table of the many link record in Purchases. While in Purchases a record shows only a link to 1 Customer.
And to make things more complicated: would it be possibile to add more conditions with references to more tables? something similar to the wrong one here below:
let a := Customer.FullName
sum((select Table2 where FullName = a) and (select Table3 where Category = b)).Amount
When you get into something like this then you have to have reference fields setup properly.
Can you describe how your tables in the above scenario are setup?
I've made a few changes you can check out.
I need to sum all the purchases made by a person, grouped by a "category" specified in the "Catalogue" table (in this sample: guitars, keyboards, drums and tech).
If you are in the the People table you already have a view of all purchases made by each person. The thing I couldn't do was put the Category field in the view. So I modified it to be able to make it viewable. I don't know why I needed to do a first when I do a record but that is what worked.
So if you add that field to the view you can now group by Category and then do a sum on Price.
I've also created a Dashboard that shows how you can get all the data as well in another table. I've created a field Guitars - Cash that shows a multi filtered search. As you are familiar with databases you just need to be able to follow a path to the data you want to filter.
So I want to find all cash payments for guitars by the person selected.
let xPerson := record(People,number(Person)); sum((select Payments)[People = xPerson and text(Method) = "Cash" and record(Catalogue,number(Description)).Category = 1].Amount)
Which means I have to be in the Payment table as that is where the Amount field is stored.
My first filter will find only records where the record selected in People matches my selection in the dashboard.
Then it will further filter for only Methods that are Cash. Since Method is a choice field I have to say I want the text of the choice otherwise Ninox defaults to the value. I could change "Cash" to the equivalent number value too.
Now the tricky part. I now only want Guitars. There is a field called Description that stores that data, but it is a Dynamic choice field. So the record command takes that selection, finds the corresponding record, then allows me to then get the data from the Category field in that record. Category is a choice field, so instead of putting text around the whole record command I'm just using numbers and guitars is 1 on your list.
I hope this helps.