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
-
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
-
Although John's solution is the neatest, to understand your formula, this also works:
let me := CustID;select Quotes where CustID = meSteven
-
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. :)
-
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.
-
Both work a treat !!! :-)
Content aside
- 3 yrs agoLast active
- 5Replies
- 332Views