0

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

null
    • Ninox partner
    • RoSoft_Steven.1
    • 4 yrs ago
    • Reported - view

    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

    • NetSol Co., Ltd.
    • Bernd_Krell
    • 1 yr ago
    • Reported - view

    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?

    • Fred
    • 1 yr ago
    • Reported - view

    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.Client

    Here 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.

    • NetSol Co., Ltd.
    • Bernd_Krell
    • 1 yr ago
    • Reported - view

    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.

    • Fred
    • 1 yr ago
    • Reported - view

    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
      • NetSol Co., Ltd.
      • Bernd_Krell
      • 1 yr ago
      • Reported - view

      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.

      • Mel_Charles
      • 1 yr ago
      • Reported - view

      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 

       

      Test Contact selector.ninox

      Mel

    • Fred
    • 1 yr ago
    • Reported - view

    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.

    • NetSol Co., Ltd.
    • Bernd_Krell
    • 1 yr ago
    • Reported - view

    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 🙏🙏

    • Mel_Charles
    • 1 yr ago
    • Reported - view

    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 !

    • Fred
    • 1 yr ago
    • Reported - view
    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.Contact
    

    would 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
  • 986Views
  • 3 Following