Filtering contacts from a specific client
Hello :),
I have a Parent Table for my Clients. I have created a child table for my contacts, so I can record them under each specific client.
I have linked my Parent Table for my clients to my Parent table for my Quotes. When I go in my Quote table I can select a client from my clients table. However, when I try to select a contact in my Quote table it gives me all the contacts from all my clients.
What can I do to only have the contacts related to the client I choose for my Quote? Is the issue due because the contact table is a child table? I have tried so many things that I am now confused..
Thank you in advance
11 replies
-
Hi, have a look here in the topic 'Constraints' :
https://ninoxdb.de/en/manual/tables/table-references-and-relations
or do a forum search on constraint...
Steven
-
I was searching for the same issue. Sadly the link KSRoSoft_Steven posted doesn't work anymore. I looked for other "constraints" related posts but couldn't find anything which addresses my issue. Anyone can help?
-
Constraints are not very obvious. Since you didn’t tell us what your fields are I’ll use the example from this original 3 year old post. When you open the constraint window on the left hand side you would see something like:
a.Quotes b.Contacts“a” is the table you are in.
”b” is the reference field you are trying to constrain.
So you just need to match on Client:
a.Client = b.ClientHere is the link to the constraints. Scroll down till you find the right paragragh.
Next time please start a new post, so you can mark a question “answered” if you do get it answered.
-
Fred Thank you and sorry that I posted in here. I did so because it's the same issue.
Also following you advice didn't help.
I have a CLIENTS table and a subtable CONTACTS with the contact persons of each client.
Then I have Quotes and inside their I have a reference field to CONTACTS named ATTN which is a Combo Box.
If I got to ATTN and Contraints, that's what I get.
I tried many variations as I am not sure what to select, mainly under b (Contacts).
Just the field "Name"? Doesn't work. Or Company / Contacts / Name? Doesn't work neither.
I completely confused :(
I guess the data modell is not much of a help either.
-
You are trying to constrain ATTN, so you need to use another field, which in the Quotes table would be the field called Customer. I’m guessing Customer is a reference field to Company, so you would use this field to constrain ATTN.
You can try:
a.Customer = b.Company-
Fred
"Customer" is a table
"Company" is a field in the "Customer" table for the company name.
I tried every combination as no success.
I then created a new very simple database for that purpose and and even there I can't get it working. I start to hope that it's a bug in Ninox rather than me being dumb LOL.
-
Bernd Krell If I have understood you correctly then i think you are looking for this !
I have setup test.... as per your needs
see attached sample database
a customer table with company field - containing 2 records
a contacts table - containing 3 contacts (split over 2 companies)
a quotes table - with 2 records
in quote I have the field called ATTN that must only show the contact that belongs to the related company
Thus test1 company has just 2 contacts James and Susan - these are set up on the contacts table and test has just another contact (bob)
in quotes - ATTN gives those 2 contacts etc
so set setup your tables references as usual
then in ATTN field i have - both dynamic values AND dynamic value name filled in
in trigger after update i have a script to copy the telephone of the contact from the customer card etc but you mod this to copy address/email etc
Sample db attached ....Hope this helps
Mel
-
-
Mel shows a good use of the dynamic choice field.
If you want to stick with your example, take a look at my sample. Just select the Quote table. The other tables are from another test.
You will see that the code for ATTN is:
a.Company = b.Company
You can change the name for the reference field in Quotes for Company to Customer but it won’t really change the results.
-
Fred and Mel Charles Thank you both for your help. I've learned a lot from it. It still didn't work on my end until I rebooted my team. Worked right after that. Not sure this was the issue all along, but as mentioned, I gained a lot from your posts and the sample databases. Thanks again
-
Fred - I like your approach too - straight forward yet elegant! I quite like my approach by using the dynamic choice if only that if i wanted to edit the contact to select another one, I could just do it and not have to click the linked field to clear it and then select a new one (yes I know no big deal but ....) - however what do you think the limitation would be if i scaled this up to say over 2000 companies with between 1 and 4 contacts per customer- do you reckon there would be some degrading of performance? in my method versus your method of linking the two tables?
what say you ?
Bernd Krell - Glad you are sorted !
-
Mel Charles said:
however what do you think the limitation would be if i scaled this up to say over 2000 companies with between 1 and 4 contacts per customer- do you reckon there would be some degrading of performance? in my method versus your method of linking the two tables?Very good question. I would think that the implied filter of:
Customers.Contactwould be very fast.
I don't think you would see any performance hit with the constraint method until maybe you hit 10,000 or 100,000 companies. But what do I know.
Content aside
- 1 yr agoLast active
- 11Replies
- 992Views
-
3
Following