Discussions

Expand all | Collapse all

Formula text for preferred approver

Jump to Best Answer
  • 1.  Formula text for preferred approver

    Posted 30 days ago
    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
    ------------------------------


  • 2.  RE: Formula text for preferred approver
    Best Answer

    Posted 30 days 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
    ------------------------------



  • 3.  RE: Formula text for preferred approver

    Posted 29 days ago
    Hi Mark,

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

    ------------------------------
    Syaeful Bahri
    ------------------------------



  • 4.  RE: Formula text for preferred approver

    Posted 29 days 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
    ------------------------------



  • 5.  RE: Formula text for preferred approver

    Posted 29 days 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
    ------------------------------



  • 6.  RE: Formula text for preferred approver

    Posted 29 days 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
    ------------------------------



  • 7.  RE: Formula text for preferred approver

    Posted 24 days ago
    Thanks Don, really appreciated

    ------------------------------
    Syaeful Bahri
    ------------------------------