Queries from secundairy tables, searching in related tables, many to many

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

Hi,

I'm relative new to QuickBase and perhaps my question is already answered before also I'm not sure how to discribe and name my challage and as a result where to look for the sollution.

I'm trying to build an app where this problem will occur multiple times on various tables and I hoped not to get pulled into a lot of coding and scripts.

Here we go:

desired example structure looks like

  • shops <1/many>> shopsXcontacts <<many/1> contacts
  • shops <1/many>>shopsXcompanies<<many/1> companies
  • companies <1/many>> contacts

Fields

  • Shops contain =  name, fysical address, etc
  • Contacts = names, personal conatct details, role/function
  • Companies = names, Company _Type, legal details & address
  1. Company_Type makes the difference between 'own' shops, customers and suppliers.

I have created a webpage that shows on Shop level (per shop):

  1. own legal details  plus intercompany relationships (holdings etc) in tables (reports)
  2. all companies that are customers in a table (report)
  3. all companies that are suppliers in a table (report)

THE CHALLANGE:

I like to to the same for the contacts listed per shop:

  1. contacts that are related to the own shop (=legal entity =staff)
  2. Contacts that are related to companies with type "supplier"
  3. Contacts that are related to companies with type "Customer"

Trying this I thought to create a report in the table shopsXContac where I created a report that shows the related company ID but not the related company_type content which I need to use as a filter to seperate staff, end customers and suppling contacts.

When I do this the column remains blank and there seems to be some kind of loop error: <columns from a related table> | select table companies | select Company_Type | message "The field Company_Type's path can't be resolved"


I consider two workarounds but prefer to solve as above if this cannot be solved in another way.

1) create 3 different table for the different company types

2) use a role/type field in the shopsXcontacs table.


Thanks for your ideas and suggestions

(and a merry X-Mas)



Photo of Jan-Willem

Jan-Willem

  • 560 Points 500 badge 2x thumb

Posted 4 years ago

  • 0
  • 1
Photo of justin_vuong

justin_vuong

  • 0 Points
Hi Jan,



I would recommend building the report in your child table (Contacts). Any fields that you need from the parent table, pass it over as a lookup field. 



If you are still having trouble, I would suggest putting in a support ticket. 



-Justin

QB Customer Care
Photo of Jan-Willem

Jan-Willem

  • 560 Points 500 badge 2x thumb
Thanks for your suggestion Justin



Is it not that in an indircet many to many relationship the table CONTACT is parent and not child??

Attached digram shows the layout I think I need and also some example records and draft of the kind of report I'm trying to achieve.


Thanks for your thoughts.

Jan-Willem


PS


I realize it X-mas time and not many of you are online reading the forum. Still I hope somebody will have some other suggestions.