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
-
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 dofor manyRec in select ManyTable where Text = oneRec.Text domanyRec.('ManyTableToOneTable(N:1)' := oneRec.Id)endend;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...
-
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).
-
Oops. Sean and I posted at the same time. He provides good info!
-
Dean, looks like we did it again! -
When in doubt always listen to Sean!
-
Uh oh, I better watch what I say around here! Thank you Dean -
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)
-
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
-
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.
-
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
endSo 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
Thanks again in advance for all the help
-
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!!!
-
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
- 2 yrs agoLast active
- 12Replies
- 1758Views
-
2
Following