0

Linking tables after importing from database

I have imported several tables via CSV and would like to link them afterwards. I have a unique ID (company name) which I could use as a link. How can I link tables that were already linked in another software?

15 replies

null
    • Fred
    • 1 yr ago
    • Reported - view

    can you give us a bit more information?

    what are the table names?

    what are the reference field names in the tables?

    • bonniegruber
    • 11 mths ago
    • Reported - view

    Apparently andreasreitz either found an answer or gave up. But I still have the question. I have a main Addresses table and another table, Christmas cards, with a field "Link" which should be used to relate it to the "ID" field in Addresses. Each Addresses record may not have a link in Christmas cards, or it may have many links (one for each year). How do I make this work?

      • Fred
      • 11 mths ago
      • Reported - view

      Sounds like you need a new N:N (many to many) table. Normally you have a 1:N (1 to many) link between tables, but since you said:

       said:
      Each Addresses record may not have a link in Christmas cards, or it may have many links (one for each year).

      This new table could be called Sent Cards. It could be a child (Composition set to Yes) of Christmas cards, or if you have other card tables then it can stand alone. You would create a new table and put a reference field to Christmas Cards and Addresses. You can even add other fields, i.e. a date field called Date Sent.

      You don't say if you what format you are importing from or what "key" in your import you would be using.

    • bonniegruber
    • 11 mths ago
    • Reported - view

    Is there a way of automatically creating the records in either Christmas cards or the N:N table? It would be very time-consuming to do them one by one.

      • Fred
      • 11 mths ago
      • Reported - view

      yes, you can automate a lot of Ninox. What are you going to do? Can you post a sample DB, no personal info, so we can see how you have it setup?

      • bonniegruber
      • 11 mths ago
      • Reported - view

       I'm trying to restore the list of who I sent cards to and received cards from in previous years. I lost the details of to and from columns, but want to import the years and a yes/no for each year and person.  I deleted most of the records and fields for this example.

      Addresses format.ninox
      • Fred
      • 11 mths ago
      • Reported - view

      Now can you tell me how a record in Christmas Cards links to a record in Addresses? Is it through the field Christmas Card.Link to Addresses.Id? If not then what field in Christmas Cards do you plan on using?

      • bonniegruber
      • 11 mths ago
      • Reported - view

       Yes. I'd tried using  an ID field, but it got too confusing.

      • Fred
      • 11 mths ago
      • Reported - view

      Looking closer at your data the Christmas Card.Link field refers to numbers that are not in Addresses.Id so won't work.

      What did you have in mind to use to link Christmas Card to Addresses?

      • bonniegruber
      • 11 mths ago
      • Reported - view

       Whatever will work. 

      • Fred
      • 11 mths ago
      • Reported - view

      The DB you provided does not have info for me to work with. Is that all the fields you have in Christmas Cards?

      • bonniegruber
      • 11 mths ago
      • Reported - view

       Originally, the Christmas card table also included names and addresses, but these should not be needed if the links work, because the information is in the Addresses table.

      • Fred
      • 11 mths ago
      • Reported - view

      but since there is no link we need other info to use to create the links. you can use name or address.

      • bonniegruber
      • 11 mths ago
      • Reported - view

       The names and addresses are not unique. Could I rename the Addresses.id field to "link" or something else and make that work?

    • Fred
    • 11 mths ago
    • Reported - view
     said:
    The names and addresses are not unique. Could I rename the Addresses.id field to "link" or something else and make that work?

     No need to change field names, plus you can't change the field Id as that is a Ninox field.

    In the Addresses table, go to record 41 and create a new button and put this in the On click:

    do as server
    let t := this;
    for loop1 in select 'Christmas cards 2023 1230' where Link = t do
        loop1.(Addresses := t)
    end
end

    Line 1 tells Ninox to run the code on the server. If you use the public/private cloud then it will run on Ninox's server, which is generally faster the larger your tables get.

    Line 2 gets the current record info using recId format. This allows us to link to the record and access any fields in the record if needed.

    Lines 3 - 5 is a for loop that finds all records in Christmas Cards where the data in Link equals the current record Id.

    Then it goes through each record that is found and sets the Addresses reference field to be equal to the current record we are on.

    Line 6 ends the do as server.

Login to reply

Content aside

  • 11 mths agoLast active
  • 15Replies
  • 166Views
  • 4 Following