Skip to main content
Solved

[SOLVED] What is the best way to implement a pricing matrix - 2D-array?

  • April 4, 2020
  • 3 replies
  • 58 views
P
Forum|alt.badge.img+8

Hello,

I am migrating my company booking system into Airtable. Long story short, I provide freelancers to clients on a day basis.
I have > 200 clients, and over 100 freelancers.

My challenge is that some freelancers are charged a different price depending of the client. Right now, I have a pricing matrix on a Sheet, as for exemple :

I can"t find an easy solution to get the same result in Airtable.

Let’s say I have 3 tables : Clients , Freelancers , Prestations.

In the table Prestations, if I create a new record with Client B and Freelancer 2, i’d like the price to be automatically inserted as $200.
If a Prestation record is Client C and Freelancer 5, that’d be $250.

Would you have some suggestions?
Thank you very much!

Best answer by Pierre-Louis_Fi

Hey,

I finally found an elegant solution that fits my needs.
Since I believe pricing matrix or 2D-Array is something that could be interesting for others, here is my solution.

On Freelancers table, I have two column :

  • Freelancer_DefaultPrice (Currency)
    Ex : $250
  • Freelancer_SpecificPrice (Text)
    Ex : “ClientA->300, ClientB->350”

On Prestations table, I do the following, after having all info through lookup

Search if Client is included in Freelancer_SpecificPrice :
FIND(Freelancer_SpecificPrice, “ClientA”)

If the Client is found, I do the following steps to extract the price after “->” :

  • Get the length of the Client name
    LEN(“ClientA”)
  • Add “+2” at the length of the Client Name
    LEN(“ClientA”) + 2
  • Extract the price (hypothesis : price length is always 3 chars)
    MID(Freelancer_SpecificPrice, LEN(“ClientA”)+2, 3)

Then, I do simple conditions to show the DefaultPrice if Client is not found, and the SpecificPrice if Client is found in Freelancer_SpecificPrice.

The exact formulas has to be optimized depending on your needs, but also don’t forget to add CONCATENATED() to transform lookup value into Strings where you can apply FIND() and LEN().

I’d be glad to help those in need.

    3 replies

    P
    Forum|alt.badge.img+8

    Hey,

    I finally found an elegant solution that fits my needs.
    Since I believe pricing matrix or 2D-Array is something that could be interesting for others, here is my solution.

    On Freelancers table, I have two column :

    • Freelancer_DefaultPrice (Currency)
      Ex : $250
    • Freelancer_SpecificPrice (Text)
      Ex : “ClientA->300, ClientB->350”

    On Prestations table, I do the following, after having all info through lookup

    Search if Client is included in Freelancer_SpecificPrice :
    FIND(Freelancer_SpecificPrice, “ClientA”)

    If the Client is found, I do the following steps to extract the price after “->” :

    • Get the length of the Client name
      LEN(“ClientA”)
    • Add “+2” at the length of the Client Name
      LEN(“ClientA”) + 2
    • Extract the price (hypothesis : price length is always 3 chars)
      MID(Freelancer_SpecificPrice, LEN(“ClientA”)+2, 3)

    Then, I do simple conditions to show the DefaultPrice if Client is not found, and the SpecificPrice if Client is found in Freelancer_SpecificPrice.

    The exact formulas has to be optimized depending on your needs, but also don’t forget to add CONCATENATED() to transform lookup value into Strings where you can apply FIND() and LEN().

    I’d be glad to help those in need.

    A
    kuovonne
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • April 4, 2020

    Glad you found a solution that works for you.

    I’m a bit late to the conversation, but I though I’d let you know that a common way to store data like this is to have a junction table.

    Getting that information to auto-populate in fields is tricky, but it can be done with essentially the same formula you have and and W_Vann_Hall’s tip for linking all records in a table to a single record.

    This method has a small benefit that you can enter the pricing information in a currency field instead of manually having to remember the syntax for the text field.

    B
    A
    P
    Forum|alt.badge.img+8

    Glad you found a solution that works for you.

    I’m a bit late to the conversation, but I though I’d let you know that a common way to store data like this is to have a junction table.

    Getting that information to auto-populate in fields is tricky, but it can be done with essentially the same formula you have and and W_Vann_Hall’s tip for linking all records in a table to a single record.

    This method has a small benefit that you can enter the pricing information in a currency field instead of manually having to remember the syntax for the text field.


    Thank you!
    Thanks to W_Vann_Hall’s tip to aggregate formulas into a rollup, I have tremendously simplified the task to only ONE column, which is :

    Rollup type column

    IF(FIND(Client_ID,CONCATENATE(values))>0,
MID(CONCATENATE(values),FIND(Client_ID,CONCATENATE(values))+LEN(CONCATENATE(Client_ID))+2,3),
0)

    Thanks again.

    A
    Terms & ConditionsAccessibility statement