0

conditional unique() in column

my view should select all records in elements where the column "text" is unique for one label (being a linked record).

So text "a" might appear both for label1 and label2, thus should appear for each label, but all rows with text "a" for label1 should become one single row...

It shouldn't be hard but I fail.

16 replies

null
    • Fred
    • 9 mths ago
    • Reported - view

    Are you asking for a column filter that can look at two different columns (label1 and label2)?

    To the best of my knowledge the column filter only looks in the field that it represents. You might have to create a new field that combines the two field data that you can then filter that new field/column.

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

      Ah, no, creating a dynamic view, that filters through a select statement in the "Formula". The table should show table 'elements' but filter should apply to unique(elements.text) relative to elements.'has label'.Id

      • Fred
      • 9 mths ago
      • Reported - view

      Can you post a sample DB?

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

      Is maybe this of help?

       

      It shows the view, grouped by label. I'd need the Formula that currently is

      select elements where 'Information Object' = null
      • Digital Humanities Researcher
      • Christoph.2
      • 9 mths ago
      • Reported - view

       

      Label APUD_SEDEM has the text "apud sedem" multiple times, but only one unique must be visible

      • Fred
      • 9 mths ago
      • Reported - view

      So a column shows the data that is in a field, like field 'text_c' has the data 'apud sedem'. Ninox will show what is in that field for each record that is returned. There is no way, that I know of, to tell Ninox to only show the data for the first record that is return and hide the data for subsequent records. Especially if you show all of the records, then Ninox has to show you the data that is in that field.

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

      I still think I'm not expressing myself clearly, I'm afraid.

      I was thinking of something like

      select elements where unique(text_c)=true

      I know unique is not working this way, but I feel confident that a select or elements[...] filter could somehow make this work...no?

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

      maybe related to DISTINCT...

      https://forum.ninox.com/t/x2hrtck

      • Fred
      • 9 mths ago
      • Reported - view

      So you want the view element to only show 1 instance of each value in text_c? Maybe the view should be based on value of text_c?

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

      Yes, maybe that would work. But the uniqueness to text_c is relative to the label. So on the global level text_c does not need to be unique but must be unique per label (which the table is grouped by)

    • Alain_Fontaine
    • 9 mths ago
    • Reported - view

    Is it not similar to this: https://forum.ninox.com/t/m1y3v59?r=y4yxqwa

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

      Logically, maybe yes. But I feel the "view" element in Ninox in forms behaves different than the dynamic choice field.

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

      But indeed this is the best shot so far:
       

      let t := (select elements where 'Information Object' = null);
for f in unique(t.text_c) do
    first(t[text_c = f])
end order by text_c

      But is seems to filter on text_c through all elements, and not relative to the label. I would need one more loop?

      • Alain_Fontaine
      • 9 mths ago
      • Reported - view

       maybe:

      for f in unique(t.(label + text_c)) do
    first(t[label + text_c = f])
end order by text_c
    • Digital Humanities Researcher
    • Christoph.2
    • 9 mths ago
    • Reported - view

    This Formula for a view, e.g., will try to find a match between text_c in two tables. It works, but prompts the one record that should be returned 94 times (as often as the match occurs in myIO.elements)
     

    if preview then
    let myIO := this;
    let all_elements := (select elements where 'Information Object' = null and 'has label' = first(myIO.elements.'has label'));
    for i in myIO.elements do
        for f in all_elements do
            if f.text_c = i.text_c then first(f) end
        end
    end
end

    I still have issues to fully understand how Ninox is using for and if statements in queries...

    Your help is most appreciated, especially by our PhD students!

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

    I managed!

Login to reply

Content aside

  • Status Answered
  • 9 mths agoLast active
  • 16Replies
  • 186Views
  • 3 Following