Forum Discussion

AshleyAsante's avatar
AshleyAsante
Qrew Cadet
7 years ago

Help with Formula Variable

I asked a similar question a while ago, but want to get in depth more. If anyone has any ideas on how to make this happen, or where I could learn more about how this works, I'd appreciate it.


I'm working on building a Vendor Application.

  • A Table called Companies (Company Name, Location, Division)
  • A table called contacts (one company will have many contacts)
  • A Table called Material Categories (steel, aluminum, etc)
I'd like to associate Material Categories to Companies (one company will have many material categories). 

But then I'd also like that information to somehow be related to the Contacts table.
Meaning, on the contacts table, I'd like the user be able to search "Steel", and QB would return the lists of contacts that are related to the companies that sell steel.

is there a way to connect the Material Categories to the Contacts, and through use of a Formula Variable, make that searchable on the contacts table?
Formulas and functions
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    Yes, this can be done. You will need these tables and relationships

    1 Company has many contacts
    1 Company has many Material Category Company Assignments.
    1 Material Category has many Material Category Company Assignments.
    1 Material Category has Many Focus Material categories.

    That last one is the "trick".  There will be just 1 record in that table called  Focus Material Category.  It will be Record ID# 1.  Once you create it, lock down in permissions that no one can add or delete.

    So, for example select Steel as the Focus Material Category. 

    On the last relationship, make a summary field of the [# of Focus Material Categories].  Then make a formula checkbox called [Material Category is in Focus].  [# of Focus Material Categories] >1

    Look that up down to Material Category Company Assignments.

    Then make a summary in companies of the [# of Material Category Company Assignments] where  [Material Category is in Focus]
    Make a formula checkbox field called [Company has Focus Material Category]  [# of Material Category Company Assignments]>0

    Look that up into Contacts.

    Now, on the Contacts table make a formula field with a formula of 1 called [Report Link connection to Focus Material category (=1)].

    Almost last, make a report link field type on the table  
    Focus Material Category to connect to contacts based on the Record ID# on the left and the [Report Link connection to Focus Material category (=1)].

    Set he form Properties to show the contacts on the record.

    Now you should be seeing all contacts for companies who sell steel ion that record.  :)