Forum Discussion

NirajShah4's avatar
NirajShah4
Qrew Cadet
5 years ago

Relating a maximum % for a value to change from one table to another

I've got a table of "Materials" with a field for "Cost" of each material. I want to be able to structure this so that anytime the cost of an item is modified, it cannot be modified by a maximum percentage either higher or lower.
This maximum % number would be stored in another separate table, "Material Cost Modifications"; this table houses records for 5%, 10%, 15%, 20%, and Custom.
I want to be able to have my admin user select one of these records in the Material Cost Mods table (via checkbox) and when this "Active" record is selected, I need the percentage that this number represents to be pulled into the Materials table as the maximum percentage that the cost can be modified to.

I'm pretty sure this can be done but I've given myself a headache with coming up with the relational attributes to make this work or even if an automation or pipelines would be the best fit.




------------------------------
Niraj Shah
------------------------------
Relationships
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    So the goal is to pull the value of the max down to the Materials table.  The the separate issue is how to block the save if the user edits more than the max.

    Let do just the first part first.  Then once it's working, post back and we can work on Part 2.

    Make a new table called Focus Max. Create a single record in the able and then adjust permissions, so that no one, even Administrator,  can add or delete records.  The Record ID# of the single record will be 1.

    Make a relationship where One Materials Cost Modifications has many Focus Max records.  Edit the the Focus Max Record and select a Material Cost Mod record to be Active.  Lookup any values you need, but specifically the Modification %.

    Then make a relationships where One Focus Max record has many Materials.
      For the reference field let it create one, but then edit the field properties for Related Focus ma to make it a formula numeric with a formula of 1.  Then lookup the active Modification %  down to Materials.





    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • NirajShah4's avatar
      NirajShah4
      Qrew Cadet
      5 years ago
      Thanks, Mark.

      I've got it set up so the way you outlined it and I'm able to pull in the Active % down to the Materials table. 

      The issue I am having now is that if the user changes the Active modification % to a different record in the Material Costs Modifications table how does the Focus Max record update accordingly as well? Will the user have to update which % the want to be Active in the Material Cost Modifications table and then edit the Focus Max record as well?


      ᐧ


      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        5 years ago
        When you edit the focus max record to select a Max, it will flow to every Material Record.

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