0

issue trying to get a table view to work in a non related table

'Job Dockets and 'Quotes' the many side to 'Customers' (the 1 side) in related tables

However 'Job Dockets' and 'Quotes' are not directly related to each other

However- I want to open a table view of 'Quotes' within 'JobDockets' and only show (filter) those quotes that belong to the same customer.

Both tables have the key field in each is CustID. (Ie the relationship field)

However when I add the view to 'JobDockets' I am struggling to get the right syntax to populate the table view panel to show only the related records in quotes that belong to the same customer that ‘JobDockets’ does.

Tried various syntaxs and depending on what I try - it either fails or just shows all ‘Quotes’ records

 

in the view on 'JobDockets' I've tried many varioations like :-

select Quotes where "Quotes".CustID = "JobDockets".CustID - But this FAILS!

so I conclude i'm going down the wrong track

 

I have had no issues with placing straight forward views to date (inc those on a dashboard) so I’m guessing I possibly might need to create a temp relationship from ‘Job dockets’ to ‘Quotes’?

Can the usual experts here give me a nudge in the right direction. - Please :-)

cheers Mel

5 replies

null
    • John_Halls
    • 2 yrs ago
    • Reported - view

    Hi Mel

     

    You can go from one relationship to another so create a view in JobDockets and put this line of code

     

    Customers.Quotes

     

    It goes up to the customer of the JobDocket and back doen to the customer's quotes

     

    Regards John

    • Ninox partner
    • RoSoft_Steven.1
    • 2 yrs ago
    • Reported - view

    Although John's solution is the neatest, to understand your formula, this also works:

    let me := CustID;
    select Quotes where CustID = me

    Steven

    • Fred
    • 2 yrs ago
    • Reported - view

    Just my two cents, I would encourage the use of relationships(i.e. John's method) instead of select (not saying Steven is wrong as he is just giving an example). As we know if you have too many (which Ninox can't say what that number is) select statement they slow down your database.

     

    From personal experience, I have a sports league database, I quickly ran into this issue trying to create my dashboard stat pages. Once I wrapped my head around how reference fields can lead to all your tables and how to filter them, then going back and removing any select statements that I can, my database is back to repesponding normally even though I have more stat fields and more records and will add more stats as they come to me.

     

    I may need to take a stat course, but that is another forum. :)

    • Mel_Charles
    • 2 yrs ago
    • Reported - view

    As ever thanks - Chaps

    I will try both methods

    interesting area this..

    John - It is also interesting to note that either activateing 'Index' in a field makes no differ to performance either. Yet indexing supposed to tokenise the data.

    • Mel_Charles
    • 2 yrs ago
    • Reported - view

    Both work a treat !!! :-)

Content aside

  • 2 yrs agoLast active
  • 5Replies
  • 329Views