0

sibling tables

I've been struggling with this for a while but no nearer to a solution so looking for inspiration from here.

I have a parent table (p) with 2 child tables (c1 and c2). I want to display a list of c2 records (for parent of current c1), but in which I can use a value from c1 in a calculated column. Just cannot seem to do it.

When in the c1 form, I can see the value I need to use. I have a 'view' that displays all c2 records whose parent is the same parent as the c1 I'm looking at. But in that view, I cannot access any c1 values as it doesn't know what c1 record to use, even though it's displayed quite clearly in the form that contains this view.

I need to create a view that contains the relevant c2 records fields plus an additional value as a column in each row. That way, each line could use that addn. value in a calculation.

Really hard to explain what I mean without it sounding so complicated, but anyone any clues?

7 replies

null
    • UKenGB
    • 1 yr ago
    • Reported - view

    Further explanation.

          [p]

         /    \

    [c1]     [c2]

    c1 and c2 both contain a number field

    I want to build a list of c1's p's c2 records with a calculated column c1.number x c2.number

    So c1.number is basically a constant, i.e. same for each c2 record, each of which has its own c2.number value.

    Trouble is that once in the 'view' you're just looking at c2. So you can access any of p's data, but no way that I've found to get anything from c1 as that 'link' has been lost.

    I can find out how many c1 records p has, or I can get the max or min value of c1.number, but not anything from the actual c1 record from which p was obtained and thence the c1 records.

    I could do this in SQL, but cannot see the way in Ninox.

      • Ninox partner
      • RoSoft_Steven.1
      • 1 yr ago
      • Reported - view

      UKenGB can you post a sample database we can work on ? Or screenshots of what you mean? Are the number of records the same in both childs c1 and c2? The only thing they have in common are the number fields and they have the same value for the desired links?

    • Fred
    • 1 yr ago
    • Reported - view
    UKenGB said:
    Trouble is that once in the 'view' you're just looking at c2. So you can access any of p's data, but no way that I've found to get anything from c1 as that 'link' has been lost.

     Sadly you are correct. When you are in a view element and adding a formula, you are in the other table and have lost all reference to the current record you are in. It is something we have been asking for.

    Like Steven has asked, are there any other relationship between c1 and c2 records that you can use (or create) besides the parent?

    Or another way is to use a dashboard?

      • UKenGB
      • 1 yr ago
      • Reported - view

      Fred No, p is THE link between c1 and c2, no other connection. I can build p's list of c2s either just using Ninox relationships or using a more explicit 'select …' statement, Same result.

      Unlike SQL, one cannot specify the fields/columns returned. If only I could add an additional column with a value I could specify when creating that list, then I could do the calculation I need. I'd like to be able to use a 'view' to create a list of data I specify, not necessarily based solely on a single table.

      Basically, c2 records contain a quantity field, but I want to adjust(multiply) that in the list by a value (scale) that is held in the c1 record and which can be adjusted on screen and I want the quantity shown in the list to reflect that. I can do it in a simple parent<-child relationship, but not with this. So far…

      Just had an idea. Use a dummy field in p and update p each time I 'adjust' the scale. That dummy field in p should be accessible from c2 and the calculation result should change in the list. Hmmm.

      I'll have a play with that and let you know.

    • Ninox partner
    • RoSoft_Steven.1
    • 1 yr ago
    • Reported - view
    UKenGB said:
    Unlike SQL, one cannot specify the fields/columns returned.

     Yes you can return a Json-formatted list using a select function and even specify custom names to the fields:

    let x := (select Clients).{
            FirstN: 'First Name',
            LastN: 'Last Name',
            email: Email
        };

    returns a list formatted in json, to get items from this list you can use:

    item(x, 1).FirstN

     

    Steven

      • UKenGB
      • 1 yr ago
      • Reported - view

      RoSoft_Steven Yes I tried that, but the view was always simply blank as if the source for a view HAS to be a table and it cannot work from a JSON list. I'll try again though as it would be useful if possible.

    • UKenGB
    • 1 yr ago
    • Reported - view

    Cracked it. 🙂

    I created a 'dummy' field in p, with binding set to 'Global in memory' (or words to that effect). Then when I update c1.number, the update trigger also updates p.dummy. All p records have that same value now and hence p's list of c1 records can display calculations (formulas) based on p.dummy and c2.number. It works.

    Although the actual p table doesn't reflect changes to the dummy field automatically (has been discussed elsewhere), in any views of c2 records, it IS updated as soon as that dummy field is modified. So as I edit c1.number, my calculated values in each row of the c2 view is automatically updated to show the new value.

    This is exactly what I was trying to achieve. A bit of a workaround, but a pretty good one I think as it's all in memory and relies only on setting that single p.dummy value.

    One more point I just thought of. Need to make sure when first opening that c2 view, that p. dummy is set to suit the current c1.number as if the latter's not been 'updated', p.dummy may have the old (incorrect) value.

    So I'll look into that now.

Content aside

  • 1 yr agoLast active
  • 7Replies
  • 60Views
  • 3 Following