I have 2 tables.
Table 1 :
Table 2 :
Id Nr Name
01 001 Glenn
02 001 Bruce
03 002 Tom
What I would like to obtain is a table (query/view) with the following result :
Nr Firm Id Name
001 IBM 01 Glenn
001 IBM 02 Bruce
002 Ninox 03 Tom
How do I do that?
Are you talking about creating a third table? or adding a view element to an existing table?
If the field Nr is a reference field in User to Contractor, then you can add the fields Nr and Firm to the User table default view.
If the field Nr is a simple choice field then you can add a new formula field and put this in the formula:
let curRec := this;<--this step gathers all the data of the current record you are on
first((select Contractor[Nr = text(curRec.Nr)]).Firm)<--then we go and find all records in Contractor where the Contractor field Nr equals the text results in Users field Nr. Ninox will assume that multiple results could happen so you have to tell it what to do it in case. So since you probably won't have multiple results we can tell Ninox to just grab the first results and the data in the Firm field.
Good luck and let us know how it goes.
Do you want this 3rd table to be a "dashboard" that you can use view info from other tables? You can view this video for the basics on how to create a dashboard:
If you don't want a dashboard, then what is the purpose of the 3rd table? What kind of data are you wanting to track in this new table?
I am an experienced VBA programmer and maybe I think too much in that direction.
I can create a query in VBA with several linked tables and I can select several fields from each table. I can also save this query for later use.
Now when I do a Datasheet view (View) of this query, I get a table with all the fields selected in the query.