0

Normalising existing data

I've imported a spreadsheet to a table and now want to normalise it. In SQL I would have done a 'select distinct' on a given field to generate the list of values, made a look-up table and linked to it.

However Ninox links using self-generated keys and it's less obvious how I do this. I can see how to do it with empty tables, but how to do it with existing data?

17 replies

null
    • Fred
    • 1 yr ago
    • Reported - view

    Let me see if I understand your post.

    1) you have a table (table1) that has duplicate data in a field (field1)

    2) now you want to take the data in the field, strip out the duplicates and create a new table (table2) that has a record for each distinct data (field2)

    3) then you want to link a record in table1 to the the record in table2 using the data that is in field1

      • davidjedge
      • 1 yr ago
      • Reported - view

      Fred That's exactly it Fred.

    • Fred
    • 1 yr ago
    • Reported - view

    I know someone else wrote up a nice response for this question, but I can't find it.

    Here is one possible solution. The code is in a button in Table2:

    let startTable := (select Table2);
    let Array1 := unique(startTable.text(Service));
    for loop1 in Array1 do
        let newRec := (create Table4);
        newRec.(empty := loop1)
    end;
    for loop2 in startTable do
        loop2.(Table4 := first((select Table4)[empty = text(loop2.Service)]))
    end
    

    Table2 is the start table that has a field called Service. It is a choice field. I want to take all the data that is in this field and create new records in Table4 on distinct data. Then I will link the records in Table2 to the new records in Table4. But first I have to create a new reference field in Table2 to Table4. By default it is called Table4.

    In Table2 I have 5 records with the following data selected in Service:

    Microsoft, Netflix, Ninox, Microsoft, Dropbox

    Line 1 creates a variable that selects all records from Table2.

    Line 2 creates a variable that takes all of the data in the Service field and filters out any duplicates with the unique function. I have to use the text function around Service since it is a choice field. If you have a text field already then you can drop the text function.

    So I get an array with:

    Microsoft, Netflix, Ninox. Dropbox

    Lines 3 - 6 is my first loop command. Line 3 creates a loop called loop1 and uses the array in the Array1 variable.

    Line 4, creates a new record for each item in the array.

    Line 5, will set the field called empty in each new record to be equal to the Service name that is in the loop.

    In this example it will create 4 new records.

    Lines 7 - 9 is my second loop command to link the records in Table2 with the new records in Table4.

    Line 7 starts a loop called loop2 and uses the array in the startTable variable.

    Line 8 takes each record in loop2 and sets the new reference field Table4 to be equal to the first record in a select of Table4 where the empty field equals to the text of the data in Service. I have to use the first command as Ninox always assumes a select will return multiple records even if only 1 record is returned.

    Once this is all done I can delete the field Service from Table2 as the data is now not needed anymore.

    Another way of doing this is to leave out the second loop, and update all records in Table2 using the Update Multiple Records function. I would leave out the loop part and put something like this in the Assigned calculated value for Table4:

    let t := this;
    first((select Table4)[empty = text(t.Service)])
    

    I hope this helps.

      • Rafael Sanchis
      • Rafael_Sanchis
      • 1 yr ago
      • Reported - view

        Fred Hi Fred you have some short DB to share ? with the example.

      • Fred
      • 1 yr ago
      • Reported - view

      Rafael Sadly, I don't. I reused the button for something else. Is there some question you have about the code I posted?

      • Rafael Sanchis
      • Rafael_Sanchis
      • 1 yr ago
      • Reported - view

      Fred 

      Hi Fred I import a spreadsheet of AlbumCards about European Fotball.

      The AlbumCards have the Field Teams that has duplicate data.

      What I need the point 2 and 3 of yours explanation, but I can't do it.

      • Fred
      • 1 yr ago
      • Reported - view

      Rafael Ok, so what are you trying to do in your DB?

      • Rafael Sanchis
      • Rafael_Sanchis
      • 1 yr ago
      • Reported - view

      Fred 

      1) I have a table (AlbumsCards) that has duplicate data in a field (Teams)

      2) now I want to take the data in the Teams, strip out the duplicates and create a new table (AlbumsCards2) that has a record for each distinct data (Teams2)

      3) then you want to link a record in Teams to the the record in AlbumCards2 using the data that is in Teams

      This scrip would be useful for my Project 

      Thanks

      • Fred
      • 1 yr ago
      • Reported - view

      Rafael take a look at the button in AlbumCards. It sounds like you wanted a clean Teams table and then link that to each AlbumCards. So you don't need another AlbumCards table or another Teams table.

      • Rafael Sanchis
      • Rafael_Sanchis
      • 1 yr ago
      • Reported - view

      Fred Great Fred This is Just I needed, to implement in my project for when I import from Excel the file and I have a longitud List of documents, keep the table of Disciplines crean and have the link with documents. EXCELLENT.

      • Rafael Sanchis
      • Rafael_Sanchis
      • 1 yr ago
      • Reported - view

      Fred 

      Hi again Fred, I try with another DB and the following error message appear. 

      Am I doing something wrong or am I missing something.

      • Fred
      • 1 yr ago
      • Reported - view

      Rafael Nothing that I can see is wrong.

      What happens if you get rid of the first in line 9?

      • Rafael Sanchis
      • Rafael_Sanchis
      • 1 yr ago
      • Reported - view

      Fred with this formula 

      let selAliaxis := (select DB_Aliaxis);
      let Array1 := unique(selAliaxis.ProveedorOld);
      for loop1 in Array1 do
          let newRec := (create Proveedores);
          newRec.(Supplier := loop1)
      end;
      let selProv := (select Proveedores);
      for loop2 in selAliaxis do
          first(selProv[Supplier = loop2.ProveedorOld])
      end
      

      No have related info

      • Rafael Sanchis
      • Rafael_Sanchis
      • 1 yr ago
      • Reported - view

       Fred

      I send the DB the Formula is on Tooltip for copy and place on click.

      I Will continue tomorrow 😟 Thanks Fred

      • Fred
      • 1 yr ago
      • Reported - view

      Rafael Sorry I wasn't clear. I mean to get rid of the first command in front of selProv.

      • Rafael Sanchis
      • Rafael_Sanchis
      • 1 yr ago
      • Reported - view

      Fred 

      Everything is working fine Fred, I work with other models and everything works fine, sorry for so much inconvenience. Thanks 

      • davidjedge
      • 1 yr ago
      • Reported - view

      Fred Thanks very much. Beginning to get the hang of this. So 'unique' is Ninox for 'distinct' !

Content aside

  • 1 yr agoLast active
  • 17Replies
  • 215Views
  • 3 Following