Forum Discussion

MelissaFreel1's avatar
MelissaFreel1
Qrew Cadet
6 years ago

Help with a problem

Hi all:
I am a QB builder of 2 years for a nonprofit involved in addressing homelessness and I have a problem that I need some advice on how to simply solve.

I have a table that contains values that staff must consult to include in a formula related to calculating some client assistance. I have brought the table into QB using QB sync.   Basically, the table is 3 dimensional.  Staff select a value for their formula by looking at the table and based on three pieces of information.  The type of utility of which there are 16 types, the number of bedrooms in a structure of which range from 0-5 , and the type of structure of which there are three.  By knowing these three items about a property, the staff then manual enter in the appropriate value. 

Here is a screenshot of the table: 

I have looked at cascading or conditional drop downs--too messy in my opinion with all those relationships but I will do it if necessary.  I thought about API_DoQuery but I am really new to this and would need sample in URL format. 

Basically, I want staff to be able to easily know if a structure has 1BR, is an apartment, and the utility in question is electric, then the value is "X"  I would like QB to do this comparison and not the humans in order to control human error which is what is happening currently when staff "consult" this table. 

Any help would be greatly appreciated. 


------------------------------
Melissa Freel
------------------------------
Relationships
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    Hi Melissa
    If you contact me at mark.shnier@gmail.com I will work out a time to do a screen share with you to figure this out. I have a soft spot for non profits.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    The way to do this is to set up a vertical looking table with two fields.  The first field will be in the format
    Utility-Bedrooms-Structure.  So a "compound" Key field, and then rate for that compound Key.

    so for example the first row of the table would read

    [Utility-Bedrooms-Structure(Key)]       [Rate] 
    Heating Natural Gas-0-Apartment      16

    The key to that table of rates would be that compound field. You can upload via Excel but also form manual entry you would have  formula to calculate that string, and hen have a form rule to change the Key field to the calculated value whenever they do not match and uncheck that checkbox at the bottom so that it first all the time.

    Then on your Client Assistance record where you need to lookup the rate, you will have three drop down fields for the three dimensions and then once again a formula such as

    List("-", [Utility], [Bedrooms], [Structure])

    That will be use as the reference field in the relationship where 1 Rate has many Client Assistances

    Post back if you get stuck anywhere.  I don't think it will take too long messing around in excel to coax that Rates data into a vertical format with the Key field so you can upload.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------