0

Read Data from Tables without creating manual Links

Despite extensive searches and experimantation I now concede I am stuck

I have 4 tables of data which one way or another are connected, all data comes from separate sources but are linked

Tables 1 and 3 need to be connected (read not update) tables 2 and 4

Tables 1, 2 and 3 all have a common field and I can achieve what I need but I have to manually link the records which is a not ideal as there are several thousand entries in each table.

How can I get one table to read a value from another table without having to manually link all the records ?

Not my scenario but for ease of an example If we call

Table 1 - Sales

Table 2 - Dispatch

Table 3 - Purchasing

Table 4 - Stock

I basically would like to enter a sales reference and display the dispatch details , selected purchasing information and available stock (in the real world I guess tables 3 and 4 would be one and the same or be connected in some way), many centuries ago I created a similar database in Lotus Approach (Stock Control and ordering) where I could simply join the fields between the tables and select what I needed to see the data model pretty much the same but more granular down to field level.

Table1.SalesOrder - Linked to Table2.SalesOrder & Table3.SalesOrder

Table1.SKU - Linked to Table3.SKU and Table4.SKU

What am I missing in Ninox please ?

12 replies

null
    • Sean
    • 3 yrs ago
    • Reported - view

    I know only one way to do it and that is to use some code attached to a Button. This is the code...

     

    for oneRec in select OneTable do
    for manyRec in select ManyTable where Text = oneRec.Text do
    manyRec.('ManyTableToOneTable(N:1)' := oneRec.Id)
    end
    end;

     

    The Reference must already exist between the tables and it should be created in the child table. Use the appropriate Table and Reference names for your situation. Here's a link that might help also...

     

    https://ninox.com/en/forum/technical-help-5ab8fe445fe2b42b7dd39ee7/is-it-possible-to-link-automatically-via-a-function-fields-in-two-tables-that-contain-the-same-text-5c6aaf80e850ad238982b496

    • Choices_Software_Dean
    • 3 yrs ago
    • Reported - view

    There is an excellent sample solution named "LinkTables" that shows how to do what you need. Ask Ninox for access to the team named “Webinar EN 2020” which contains the sample solution (and many others).

    • Choices_Software_Dean
    • 3 yrs ago
    • Reported - view

    Oops. Sean and I posted at the same time. He provides good info!

    • Sean
    • 3 yrs ago
    • Reported - view

    😁 Dean, looks like we did it again!

    • Choices_Software_Dean
    • 3 yrs ago
    • Reported - view

    When in doubt always listen to Sean!

    • Sean
    • 3 yrs ago
    • Reported - view

    😳 Uh oh, I better watch what I say around here! Thank you Dean

    • support.1
    • 3 yrs ago
    • Reported - view

    Thanks for the swift replies, I'll certainly take look at Webinar EN 2020, I couldn't get that button code to work, I will take another look once I am properly awake, just after I posted this question I thought I must be either imaginging it was that easy in Approach or it really was, so I found a competitors "free online database" and imported the 4 tables and bang there it was all in 10 mins all the data on all the tables as required - slow and clunky but there (I'm sure the've ported approach into a web based app for that) missing all the nice features of Ninox and performance.

    Surely once the tables are linked I should be able to place a field from that table into my view

    So assuming Table1 is the where the master view is, I should be able to say for Table1.Order display Table2.value1 for Table2.SALESORDER where Salesorder matches Table1.Order ?   

    and No I'm not going to downgrade to Windows Millenium just to run Lotus Approach (I was a Beta Tester for Approach back in the day so do know it quite well tempted just for ease but I like my MAC these days and the ability to use Ninox anywhere)

    • support.1
    • 3 yrs ago
    • Reported - view

    Tried the button text again but get stuck on line 3

     Button Code

    for oneRec in select Table1 do
    for manyRec in select Table2 where Field1 = Table1.'Field 1' do
    manyRec.('ManyTableToOneTable(N:1)' := 'Field 1' ) 
    end
    end;

    If I am reading it right it's doing this

    Line 1 = Take the current record in Table 1

    Line 2 = find the matching records in Table2 where Field1 matches Field 1 in Table1

    Line 3 = create link for matching records from Table2 to Table1 at this point I get the error regardless of what I put in

    Field Not Found ManyTableToOneTable(N:1) at Line 3 Column 38

     

    Many Thanks in Advance

    • Sean
    • 3 yrs ago
    • Reported - view

    I just got burned by the, inconsistent?, log out of the Ninox forum 🤬. I'll try again...

     

    oneRec is the variable that holds the current iteration of the first for-loop which is looping through the parent table. Be sure to use the name of your parent table in place of OneTable.

     

    manyRec is the variable that holds the current iteration of the second for-loop which is looping through the child table. Be sure to use the name of your child table in place of ManyTable.

     

    Two problems in your Line 3: First, ManyTableToOneTable(N:1) is the name of my Reference field. I made it intentionally descriptive so it would help point out the type of reference. You need to use the name of your child's table Reference field there. Second, where you have 'Field 1' it should be oneRec.Id. The code is linking Id's not the matching fields that are found in Line 2.

    • support.1
    • 3 yrs ago
    • Reported - view

    Thanks for the help so far, So to work this out I created a new database with 3 tables with very little data to simplyfy the whole thing as it was running forever (Between the tables around 150k records) and appeared to do nothing.

    for oneRec in select T1 do
    for manyRec in select T2 where T1 = T2F1 do
    manyRec.T1.(T1F1 := oneRec.Id)
    end
    end

    So I now have the button code that doesn't complain and runs through all 3 records (or at least I hope it does) but it doesn't appear to link anything. I have tried various combinations of Table references and used each common field to try and establish the link, the button code runs through but doesn't appear to do anything.

    So Just to clarify, data would be imported into all tables except Table 1 which is effectively a query page and the general idea is to be able to show at a glance the data from the other tables and then hopefully be able to create views based upon that data 

    Ninox Test Tables

     

    Thanks again in advance for all the help

    • BAJABI SL
    • jjrecort
    • 1 yr ago
    • Reported - view

    Hi all,

    I'm stuck in the same place. I have been searching for hours on how to do this. For me is the 101 of databasing.

    My case is pretty straightforward.

     

    I have a PRODUCTS table with SKU, PRODUCT NAME, DESCRIPTION, MANUFACTURER, EAN, COST, RETAILPRICE; WEIGHT, etc.

    Then I have a STOCK table with SKU, SERIAL, PRODUCT NAME, DESCRIPTION, MANUFACTURER, DATEIN, DATEOUT, STOCK LOCATION, etc.

    What I want to do is:

    When I manually enter the SKU in the STOCK table, the fields PRODUCT NAME, DESCRIPTION, and MANUFACTURER  are automatically populated from the PRODUCTS table. 

     

    As simple as that. I'm managing over 14K records, so I cannot go one by one manually searching which field. Links in NINOX are manual.

     

    I'm coming from Filemaker, and it is pretty straightforward. Graphically link the two tables connecting the two common fields (SKU) and then create a field with the source table name+desired field  (Products:ProductName); that's it.

    I was doing this 34 years ago with dbaseIII, and it was like the second lesson of the beginner's course; I don't understand why it is so complicated or hidden in Ninox.

     

    Thanks a lot!!!

    • Fred
    • 1 yr ago
    • Reported - view

    Take a look at the attached DB. It is very basic but it should point you to the right direction.

    If you decide on this path then we can talk about how to script the linking of all your records.

Content aside

  • 1 yr agoLast active
  • 12Replies
  • 1747Views
  • 2 Following