Forum Discussion

SyaefulBahri3's avatar
SyaefulBahri3
Qrew Trainee
5 years ago
Solved

Formula text for preferred approver

Hi All,

I have this condition

If purchase <= 2000 approver Diana
If purchase  > 2000 approver Rob

With this condition, I can create formula text "Approver" with this

If(
[Purchase Price] <= 2000, "Diana",
[Purchase Price] > 2000, "Rob")

The problem is I have different approver for one specific unit
There are 3 units: Communication, Trade, Economics

For Economic unit for purchase <= 2000 the approver is Melissa

This is my form structure

Unit name: multiple choice (Communication, Trade, Economics)
Requested by: text (name of the requester)
Purchase price: numeric
Approver: formula text

How can I achieve this condition?

Many thank







------------------------------
Syaeful Bahri
------------------------------
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    5 years ago
    no problem,
    Quick Base evaluates the IF statement conditions in the sequence as listed, so just deal with the more specific situations first.

    IF(
    [Specific type] = "Communications" and [Purchase Price] <= 2000, "Curley",
    [Specific type] = "Trade" and [Purchase Price] <= 2000, "Larry",
    [Specific type] = "Economics" and [Purchase Price] <= 2000, "Moe",

    [Specific type] = "Communications" and [Purchase Price] > 2000, "Wilma",
    [Specific type] = "Trade" and [Purchase Price] > 2000, "Fred",
    [Specific type] = "Economics" and [Purchase Price] > 2000, "Barney",

    [Purchase Price] <= 2000, "Diana",
    [Purchase Price] > 2000, "Rob")





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

6 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    5 years ago
    no problem,
    Quick Base evaluates the IF statement conditions in the sequence as listed, so just deal with the more specific situations first.

    IF(
    [Specific type] = "Communications" and [Purchase Price] <= 2000, "Curley",
    [Specific type] = "Trade" and [Purchase Price] <= 2000, "Larry",
    [Specific type] = "Economics" and [Purchase Price] <= 2000, "Moe",

    [Specific type] = "Communications" and [Purchase Price] > 2000, "Wilma",
    [Specific type] = "Trade" and [Purchase Price] > 2000, "Fred",
    [Specific type] = "Economics" and [Purchase Price] > 2000, "Barney",

    [Purchase Price] <= 2000, "Diana",
    [Purchase Price] > 2000, "Rob")





    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------
    • SyaefulBahri3's avatar
      SyaefulBahri3
      Qrew Trainee
      5 years ago
      Hi Mark,

      Thank you for your solution. I will give it a try

      ------------------------------
      Syaeful Bahri
      ------------------------------
  • DonLarson's avatar
    DonLarson
    Qrew Commander
    5 years ago
    Syaeful,

    I suggest you drive this with the Architecture of the application instead of hard coding it to specific people.  If you set up the application like this.


    You can now solve this with a shorter Formula User Field

    If ( [Purchase Price] > [Dollar Limit],  [Approver High], [Approver Low] )

    This does several things for you.
    You can now have as many Units as you want.  Just add Policy and the formula does not change.
    You can have different $$ for the change in Approver.  Policy can be $5K while Trade is $2K
    You can change the Approvers with out opening the formula

    More importantly now you can drive the business process and reports on the User.   When a Purchase record is created, the correct Approver can get Notifications, Dashboard updates and custom reports.


    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------
    • SyaefulBahri3's avatar
      SyaefulBahri3
      Qrew Trainee
      5 years ago
      Hi Don,

      Your solution is really interesting. This is bringing a new question about how I should designing the table.

      Since the approver is actually part of "Employee Table" and there is a table relation between Unit Table and "Employee Table" - Each Unit has many Employee. This should bring me to think a better idea for this case.

      Another condition for the approver is whenever they're on leave we need to find a replacement for the approver. I'm thinking about creating "Movement" Table to track the employee leaves and create "Replacement Table" for the approver replacement

      So this is the big picture based on the above situation for "Purchase Table"

      Unit name: Lookup - Unit Name
      Requested by: Lookup - Employee Name
      Purchase price: Numeric Currency
      Approver: Lookup - Approver
      Approver Status: Lookup - Movement

      Do you have solution for this condition Don?

      Many thank






      ------------------------------
      Syaeful Bahri
      ------------------------------
      • DonLarson's avatar
        DonLarson
        Qrew Commander
        5 years ago
        Syaeful,

        This gets a bit more complicated since the User needs to change based upon some set of availability rules.   Those rules I am sure can get very complicated based upon Movement, Total Work Load and a myriad of other factors.  Here is potential architecture for you.


        • The Units table knows who the next Approver should be based upon the Available Status from the Employee Table.
        • The Purchase Table will know the Reference Field of those Employees when it is related to the Units Table.
        • Trigger an Automation to relate the Purchase Table to the Employees table so that the Approvers are now fixed.
          • You also have a way to override that regardless of Available Status
        • The Units table will update who the Next Approver is because the Employees Available Status has changed.
        Good luck with this.  Elegant ways to assign projects and tasks can get complicated., particularly if commissions and performance reviews are impacted.  








        ------------------------------
        Don Larson
        Paasporter
        Westlake OH
        ------------------------------