0

Group by equivalent

I'm trying to gather records from a table with e.g. 3 fields (columns). Let's say field1, field2 and field3. The best way I can describe what I want is by pseudo Ninox/SQL like:-

SELECT field1,field2,concat(field3) FROM tablename GROUP BY field1, field2

 

Basically, field1 and field2 contain many duplicate values and I want only unique combinations of them both, but also with all the field3 values of those unique field1 and field2 combinations.

 

To clarify, this needs to be an actual array of the relevant data that I can process and use, so Ninox's GUI method of grouping in Views etc is of no use.

After many hours of pondering this thorny issue, I've failed to come up with any way to achieve this in Ninox without any 'for loops' iterating through a previous selection.

Can anyone suggest a solution?

7 replies

null
    • Ninox partner
    • RoSoft_Steven.1
    • 2 wk ago
    • Reported - view

    Maybe JSON is the way to go....

      • UKenGB
      • 2 wk ago
      • Reported - view

      Er, could you expound on that?

    • Ninox partner
    • RoSoft_Steven.1
    • 2 wk ago
    • Reported - view

    Once you have a json variable, you can handle this like a table with records.

    To create a json variable, you can use something like this:

    let jsonvar := (select Tablename).{
field1 := tablefield1;
field2 := tablefield2;
} order by tablefield4

    Now you can use this like:(to create records in Table5)

    for i in jsonvar do
let p := create Table5;
p.(field1 := item(jsonvar,i).field1;
   field2 := item(jsonvar,i).field2)

    or have different values in a formula field:

    first(jsonvar).field1
item(jsonvar,0)field2

    All depends on what you want to do with this "process and use"

    • Fred
    • 2 wk ago
    • Reported - view
     said:
    Basically, field1 and field2 contain many duplicate values and I want only unique combinations of them both, but also with all the field3 values of those unique field1 and field2 combinations.

     Can you describe an example? Or can you post an example DB?

    • UKenGB
    • 13 days ago
    • Reported - view

    Thinking of this as family relationships (i.e. child, parent grandparent etc) I'm trying to create a list of all unique firstnames of my 'nephews' (i.e. my parents' grandchildren that are not mine) with a concatenation of all middlenames used by those nephews with each firstname.

    So far, I use relationships (i.e. no SELECT) to gather a list of nephews (sorted by firstname) which I then iterate through in a for loop, only selecting the first record with each firstname. That gives me the list I need, although would prefer to get it all in one shot without needing the for loop, but not so far been able to achieve that.

    I am then trying to create a column in the View element displaying this list that concatenates all the midlenames associated with each firstname and I have discovered a problem with that. I need to know ME to exclude my children's midlenames, but the context of the View is of children and not MY record so I cannot seem to access ME and be able to exclude my children.

    I seem to have come across this issue before and not sure I ever solved it. Can a column of a View whose rows have no direct relationship to the current record, access the actual current record in which the View is being used?

    • UKenGB
    • 13 days ago
    • Reported - view

    Using a 'per record in memory' field as a variable to store the current nid, I've been able to construct the correct concatenation, but I've had to include a required button press as that's the only way to set a value. No other Ninox trigger can set a value. Ninox just complains if you try to set a value in anything that gets run just when changing the current/active record.

    So I got there in the end, but there are definitely some improvements that could be made to Ninox, like a built-in 'onselect()' function that we can use to detect when a different record is selected in a View (of either type).

    In case anyone thinks I have a weird family, the above explanation was simply to explain a data structure that in reality has nothing to do with people at all. 🙂

Login to reply

Content aside

  • 13 days agoLast active
  • 7Replies
  • 49Views
  • 3 Following