0

loop in query for view -- how to aggregate?

I use the below formula for a view and a dMC. Yet, it will only return the last record i, not all of them. When I create a button to alert all i, it will prove the loop works correctly. How does the view/dMC aggregate all i?

let my_event := this;
let my_entries := this.'is testified by'.entry;
let my_benefices := (select Benefices);
for n in my_entries do
    let my_benefices_n := (select Benefices where testimony.entry = n);
    for i in my_benefices_n do
        i
    end
end

9 replies

null
    • Digital Humanities Researcher
    • Christoph.2
    • 9 mths ago
    • Reported - view

    This formula below works for the current case -- but is a loop in such a formula really impossible to use?
     

    let my_entries := this.'is testified by'.entry;
    select Benefices where cnt(testimony[contains(my_entries, entry)]) > 0
    
    • Alain_Fontaine
    • 9 mths ago
    • Reported - view

    If you want to see what the script really returns, put it in a formula field, and put the "for" loop inside a "debugValueInfo()" function. You will see that it does not return an array, but an array of arrays…

    • Fred
    • 9 mths ago
    • Reported - view

    You may want to look at this post to compare two arrays.

    • Digital Humanities Researcher
    • Christoph.2
    • 9 mths ago
    • Reported - view

    I didn't put clearly enough what I'm after. I need something like pandas.DataFrame.explode in python -- to flatten an array of arrays.

    The post posted by will go some way towards a solution, I feel.

    In my formula above, I get 3 i-records for n=1 and 2 i-records for n=2 (if my_entries returned only two records), for example, so my "view" should aggregate all 5 records.

    • Fred
    • 9 mths ago
    • Reported - view

    So you want to end up with records in Benefices where the entry equals the entries of the current record?

    Wouldn't this work?

    let my_event := this;
    let my_entries := unique(this.'is testified by'.entry);
    for n in my_entries do
        select Benefices where testimony.entry = n
    end
      • Digital Humanities Researcher
      • Christoph.2
      • 9 mths ago
      • Reported - view

      works if there is only one benefice for the entry in questions (this.'is testified by'.entry) but once there are two, it shows none. There are, however, two rows in testimony that have entry=this.'is testified by'.entry.

      This formula below still works correctly, but this post was also about understanding the loop/array mechanics.

      let my_entries := this.'is testified by'.entry;
      select Benefices where cnt(testimony[contains(my_entries, entry)]) > 0
      
    • Alain_Fontaine
    • 9 mths ago
    • Reported - view

    Arrays of arrays are strange objects in Ninox. They sort of exist, since they can be produced by loops, but they act strangely when one tries to manage them by the usual functions. To flatten such a beast, if you are sure that the values of the elements never contain a comma, you can try:

    split(join('Input array of arrays', ","), ",")
    
    • Digital Humanities Researcher
    • Christoph.2
    • 9 mths ago
    • Reported - view

    thanks! I will check out both solution on the weekend, and mark this thread as answered!

    • Ge the best email database management tools
    • Michael_Hooper
    • 5 mths ago
    • Reported - view

    working...thanks for sharing.

Content aside

  • 5 mths agoLast active
  • 9Replies
  • 107Views
  • 4 Following