0

Query

Hi,

I have 2 tables.

Table 1 :

Contractor :

Nr        Firm

001      IBM

002      Ninox

.........

Table 2 :

User :

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?

Tx

Regards

Michel

4 replies

null
    • Fred
    • 2 yrs ago
    • Reported - view

    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.

    • Michel Verheyen VOF
    • Michel
    • 2 yrs ago
    • Reported - view

    Hi,

    Tx for your reply.

    With the reference field it works fine, but if I want to create a third table how should I do it! My table remains empty.

    Tx

    Regards

    Michel

    • Fred
    • 2 yrs ago
    • Reported - view

    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:

     

    https://www.youtube.com/watch?v=nUqGl84gh08

     

    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?

    • Michel Verheyen VOF
    • Michel
    • 2 yrs ago
    • Reported - view

    Hi,

    Thanks again.
    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.

    Regards

    Michel

Login to reply

Content aside

  • 2 yrs agoLast active
  • 4Replies
  • 183Views