0

Display associated content with conditional constraints.

In table HOME, there is a record with "nb" as 1. I want it to display the associated content with table copy, but without showing duplicate names, similar to what is shown in "show" (but excluding duplicate names). What are some ways to present this? Code or otherwise?

20 replies

null
    • Alan_Cooke
    • 5 mths ago
    • Reported - view

    I think the 'unique' parameter will do the job

    unique | Ninox Docs (EN)

      • gold_cat
      • 5 mths ago
      • Reported - view

       Thank you, my friend. I have solved the problem.

    • Fred
    • 5 mths ago
    • Reported - view

    As you can see there is no way to modify the many (N) side of a reference link. This field shows you all records linked to the current record. It is normally shown as a table, so you can see all linked records.

    If you only want to see a unique list of the data in a particular field of a reference table then you will need to create a formula field that does that. Something like:

    unique(Show.name)
    
      • gold_cat
      • 5 mths ago
      • Reported - view

       Thank you, fred. I have solved the problem.

      • Fred
      • 5 mths ago
      • Reported - view

      can you post your solution so others can learn from your wisdom?

      • gold_cat
      • 5 mths ago
      • Reported - view

       

      1.  said:
        can you post your solution so others can learn from your wisdom?

         Thank you, Fred. I applied the “unique” code you provided.

      2. I would also like to write a piece of code in the copy table such that if "coffee" appears 3 times, the first occurrence of "coffee" is marked as 1, and the other two are marked as 0.

        Of course, this also applies to all duplicate values within the table.

        How should I write this code?

      3.  
    • Fred
    • 5 mths ago
    • Reported - view
     said:
    I would also like to write a piece of code in the copy table such that if "coffee" appears 3 times, the first occurrence of "coffee" is marked as 1, and the other two are marked as 0. Of course, this also applies to all duplicate values within the table. How should I write this code?

    You can have this done at the table level at the Trigger on new record. Or if you have a button that creates the records then it can be done there.

    Since you are testing, create a button in the copy table and try writing code that:

    1) creates an array of all records that have "coffee" in a the field.

    2) create a variable that counts the length() of the array from step 1.

    3) create a for loop using the range command that starts with 0 and ends with the variable from step 2.

    3a) inside the for loop use an if statement that checks if the loop variable is equal to 0 then it puts 1 in the field, else it puts 0 in the field.

    If all this works then we can work on removing the hard coding of "coffee" and make it more dynamic. Hint, another variable and a parent for loop.

      • gold_cat
      • 5 mths ago
      • Reported - view

       

      let xxx := length((select copy).name);
      for i from 0 to xxx do
          if i = 0 then 1 else 0 end
      end

      "Is it like this? It's not quite right in my database..."

    • Fred
    • 5 mths ago
    • Reported - view

    You wrote:

    let xxx := length((select copy).name);
    for i from 0 to xxx do
        if i = 0 then 1 else 0 end
    end
    

    Here are the steps I suggested:

    1) create an array of all records that have "coffee" in a the field.

    Add a variable for this:

    let coffeeRecs := select copy where name = "coffee";
    

     

    3) create a for loop using the range command that starts with 0 and ends with the variable from step 2.

     

    Well it looks like I was wrong about this. We need to use the coffeeRecs variable as we need access to the record Ids so we can modify fields in the records.

    So the for loop would look something like:

    for loop1 in coffeeRecs do
        let recIndex := index(coffeeRecs,loop1);
        loop1.fieldname := if recIndex = 0 then 1 else 0 end
    end
    

    fieldname is the fieldname in the copy table you want to store the data of 1 or 0.

    The final code would look like:

    let coffeeRecs := select copy where name = "coffee";
    for loop1 in coffeeRecs do
        let recIndex := index(coffeeRecs,loop1);
        loop1.fieldname := if recIndex = 0 then 1 else 0 end
    end
    
    
      • gold_cat
      • 5 mths ago
      • Reported - view

       

      Great job on the work, thanks Fred. However, there's a small issue. For example, in the 'copy table,' 'beverage' is also considered a duplicate item. How can we achieve the same effect for these as well? Because the code currently needs to define each duplicate item, is it possible to implement this function programmatically?

    • Fred
    • 5 mths ago
    • Reported - view
     said:
    For example, in the 'copy table,' 'beverage' is also considered a duplicate item. How can we achieve the same effect for these as well?

     That was the next step in the process. You need to:

    1) create a new variable that stores the unique value of the 'name' field. This creates an array of values that we can now search on with a for loop. Now that you are putting a loop inside a loop, I find it helpful to name the loop variable loop1 and loop2.

    2) create a new for loop that surrounds the current code. Since we used loop1 already, we can call this one loop2.

    3) then you need to change the where in the select from "coffee" to the new loop variable, loop2.

    4) you can change the variable 'coffeeRecs' to something else that makes more sense, but for now you can leave it.

      • gold_cat
      • 5 mths ago
      • Reported - view

      Although these are too difficult for someone like me who doesn't understand code, you've helped me improve. Thank you.

      let onlyname := unique((select copy).name);
      for loop2 in onlyname do
          let thanksFred := (select copy where name = loop2);
          for loop1 in thanksFred do
              let recIndex := index(thanksFred, loop1);
              loop1.(fieldname := if recIndex = 0 then 1 else 0 end)
          end
      end

      Better teach a man how to fish than give him fish; Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.

      • Fred
      • 5 mths ago
      • Reported - view
       said:
      Although these are too difficult for someone like me who doesn't understand code,

      It wasn't that difficult as you seem to have no problem figuring it out. You seem to pick things up quickly so you will have no problem with Ninox. Remember this is a low-code environment. If I can do it anyone can.

      Here is another task: looking at the code, we do two selects of the same table. With such small tables that is not a problem. But as a quiz, can you write the code in a way that only does 1 select? Hint: you will be using square brackets instead of the where.

      • gold_cat
      • 5 mths ago
      • Reported - view

       

      I encountered another problem, Fred. I initially thought of using fieldname=1 or 0 to differentiate between duplicate names, but my home table also has a Y&N filter condition. For example, 'beverage' should also appear in the first record of HOME's Show. How should I optimize it?

      • gold_cat
      • 5 mths ago
      • Reported - view

       

       let Password := 'code';
      let aa := (select 'code' where Password = 'Stored password');
      select 'Users' where number('ID') = aa.'User'

      Hi Fred, it seems like I encountered the case you mentioned today, where we can avoid using "where" clause. This code without "for" might help me understand the usage of square brackets better. Could you please guide me through it?

      • Fred
      • 5 mths ago
      • Reported - view

      Well, you have to decide how you want to deal with the 'and' part of your code:

      let aa := this;
      (select copy where 'Y&N' = aa.'Y&N' and fieldname = 1) order by -appearances

      In record 1 of Home, the Y&N is set to 'No' and there is no record in copy that matches both Y&N and fieldname with a 'name' field of 'Beverages'.

      So you need to figure out what is more important:

      1) matching Y&N and fieldname

      or

      2) matching Y&N

      or

      3) matching fieldname

       

      I would also start thinking about what you are trying to solve and maybe there is another way going about it.

      Can copy be made a child of Home?

      Can copy be N:1 linked to Home?

      Is name supposed to be some sort of category list, which you can then make a new table then either direct link or dynamic link to copy.

    • gold_cat
    • 5 mths ago
    • Reported - view
     said:
    looking at the code

    I can only do this....I'm sorry, I don't know how to start because I haven't understood the purpose of the square brackets yet.

    • Fred
    • 5 mths ago
    • Reported - view
     said:
    I don't know how to start because I haven't understood the purpose of the square brackets yet.

    Square brackets can be used in place of where in selects and to filter out any array.

    let coffeeRecs := select copy where name = "coffee";
    
    //would turn into
    
    let coffeeRecs := select copy[name = "coffee"];
    

    The thing to know about square brackets is that in a select statement, the client copies over the entire table in the select then filters. Not a problem if you have a small dataset, but a problem if you have a large one.

    But if you find yourself doing selects to the same table but filtering it differently then a way around this is to do 1 select into a variable then filter the variable as you move through your code.

    let getAllRecs := select copy;
    let filterCoffee := getAllRecs[name = "coffee"];
    let filterGrapes := getAllRecs[name = "grape"];
    
      • gold_cat
      • 5 mths ago
      • Reported - view

       Learn it and understand it slowly. Thank you

    • gold_cat
    • 5 mths ago
    • Reported - view
     said:
    Is name supposed to be some sort of category list,

    The actual situation is like this: I just simplified the steps to make it in text form.

     said:
    Can copy be made a child of Home?
    Can copy be N:1 linked to Home?

    What are the aims of these two steps? 

     said:
    So you need to figure out what is more important:

    I think HOME might serve as a dashboard, using its Y&N status to filter matching Y&N entries in the copy table, and then tally the number of identical values in the name field of the copy table.

Content aside

  • Status Answered
  • 5 mths agoLast active
  • 20Replies
  • 168Views
  • 3 Following