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...
16 replies
-
Hi Gianluca
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]).FieldBRegards John
-
Are you creating this new sum formula in Table2 or Table1?
John's formula works if you are in Table1.
If you are in Table2 then you have to use the select function.
let a := this; sum((select TABLE2[FieldA = a.FieldA]).FieldB) -
John Halls and Fred Thank you!
Il try both solutions today and let you know.
-
Gianluca said:
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:
Gianluca said:
I created a N:1 relationship between TABLE1 and TABLE2Which 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):
Table2.fieldBNinox is fine with this.
The moment I add the square brackets, because I want to filter the relationship,
Table2[].fieldBNinox 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.
-
Gianluca said:
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)).AmountWhen 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?
-
Fred
here's a sample database, with few tables and fictional data (hope you like good music and beautiful women
). It's not the actual database I'm trying to build (which is about managing finances and creating a custom invoice system in my school, therefore has a lot more "complications") but it's good enough to represent my needs at this step of the process. 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). In Filemaker I Used to do this with the if/then or case function and I thought that could be a good approach in Ninox too, but I'm having unexpected troubles with the logic behind it. In filemaker I would set a few variables and use the foreign key of a table (which is different from table to table even if refers to the same data) to define all the data I need. In Ninox, I am not able to identify which fields or variable to use.
I don't mind where it happens: it could simply be new formula fields in People table or I can create a new table (eg. "balance") where all the data regarding people, their purchases and their payments will be gathered
What is important for me is to understand how to set multiple conditions (i.e. sum all the purchases made by Richie only if a guitar has been purchased. Same for keyboards, drums and tech.).
So the conditions are: same person (data taken from People table), same category of purchase (data taken from the Catalogue table).
The final formula will need to have way more conditions, but I think that once I've learned to set two, I will have no problem setting 100 using the same logic...at least I hope )
-
-
I’ll get a sample database ready and post it
i Guess it’s easier than trying to explain 
-
I've made a few changes you can check out.
Gianluca said:
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.
Content aside
- 2 yrs agoLast active
- 16Replies
- 636Views
-
3
Following