0

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

null
    • John_Halls
    • 1 yr ago
    • Reported - view

    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]).FieldB

    Regards John

    • Fred
    • 1 yr ago
    • Reported - view

    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)
    
      • Gianluca
      • 1 yr ago
      • Reported - view

      with both formulas (used in both tables) I get this error: "Expression does not return multiple values". I'll try to explain more in details what I need because maybe I wasn't clear enough in the previous message.

      Table1 is a list of customers and their personal data.

      Table2 is a list of all their purchases  - with customer's name (coming from Table1), purchase name, cost and purchase type.

      In Table1 for each customer I need a formula field to sum the amount of all their purchased services.

      I can eventually set up a third table, if really needed but for the purpose I fell like it's one table too much.

      • John_Halls
      • 1 yr ago
      • Reported - view

      Gianluca Hi, it won't return multiple values because you are looking for the sum of the amount. This would go in a formula field. Now that you have explained a bit more the formula is simpler than before, it's just

      sum(Table2.Amount)

       Regards John

      • Gianluca
      • 1 yr ago
      • Reported - view

      John Halls thanks, but that way I get the amount of all the purchases in Table2, correct?

      I only need the purchases of each customer, so I guess it's gotta be something like:

      let a := Customer.FullName
      sum(select Table2 where FullName = a).Amount

      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

      or should I necessarily create a new join table where to get all the conditions from?

      • John_Halls
      • 1 yr ago
      • Reported - view

      Gianluca Wherever you have a relationship between tables you can use the name of the relationship field to access related data. In my bit of code

      sum(Table2.Amount)
      

      Table2 is not the name of the table but the name of the relationship field. By using this it automatically scopes the array to those related to the parent. This is a fundamental principle in Ninox.

      Scoping has to be done using square brackets [] rather than the where clause and does not need additional select statements.

      Regards John

    • Gianluca
    • 1 yr ago
    • Reported - view

    John Halls  and Fred Thank you!

     Il try both solutions today and let you know.

    • Fred
    • 1 yr ago
    • Reported - view
    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 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):

    Table2.fieldB
    

    Ninox is fine with this.

    The moment I add the square brackets, because I want to filter the relationship,

    Table2[].fieldB
    

    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.

    • Fred
    • 1 yr ago
    • Reported - view
      • Gianluca
      • 1 yr ago
      • Reported - view

      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 )

    • Gianluca
    • 1 yr ago
    • Reported - view

    I’ll get a sample database ready and post it 😃 i Guess it’s easier than trying to explain 😉

    • Fred
    • 1 yr ago
    • Reported - view

    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.

      • Gianluca
      • 1 yr ago
      • Reported - view

      Fred Awesome. I'll study it :)

      • Gianluca
      • 1 yr ago
      • Reported - view

      Fred I'm trying to replicate your settings but....how have you managed to make the category field viewable in the purchase view inside People Table? I've set the the column to be viewed, but no...it isn't.

      • Fred
      • 1 yr ago
      • Reported - view

      take a look at the category field in Purchases. If I remember correctly I used the record command. 

      • Gianluca
      • 1 yr ago
      • Reported - view

      Fred ok...I managed.

      Now... this may be a question for support, but maybe you know the answer: why would a formula field need a special formula for its column to be displayed in views?  Aren't all the fields supposed to be viewable, if selected?

Content aside

  • 1 yr agoLast active
  • 16Replies
  • 520Views
  • 3 Following