Get column name based on the choice and use it for reference

Ask for Help
1

Hi guys!
Have you ever needed to choose different reference column names for formula?
I will try to desribe what I want to achieve.

I have table for products and prices, but I need more than just 1 price for each product and then I have a choice column which could switch what column to choose for the formula.
Each price column depends on which date it was published, so I don´t want to have all prizes actual, because I want to compare old prize and new one.

formula is something like: products.price * number
I need my formula to dynamicaly change “.price” to “.price5” or whatever I choose from choice column.

Is it possible? I can´t get it to work :slight_smile:

2

You should be able to use the following Python to grab a dynamic column name: getattr(rec, 'price'+$choice) * $number

rec is a special variable for the current row (“record”)

getattr lets you do rec.price5 with price5 as a string instead of as a literal property of the record: getattr(rec, "price5") == rec.price5

You can combine a base string with a column value (or just use a column value) as the second attribute of getattr: getattr(rec, "price"+$choice)

Then, once you have the dynamic column’s value, you can do anything else with it, like multiplying it by $number

1 Like
3

Hi ! I’m trying to do pretty much the same thing but the difference is that prices are stored in a separate table…

To be a little more specific :

  • i have a MALTS table which contains one type of malt per line, and a PRICE column for each year’s price
  • a recipe table which contains :
    • 15 columns : $NOM_MALT_1 to $NOM_MALT_15 which refer to the MALTS table
    • 15 columns : $LOT_MALT_1 to $LOT_MALT_15 which si manually filled with the serial number, which contains the year of production
    • 15 columns $MALT WEIGHT_1 to $MALT_WEIGHT_15
    • a total malt price which should return the result of the following formula :
      $NOM_MALT_1.PRICE*$MALT WEIGHT_1 + $NOM_MALT_2.PRICE*$MALT WEIGHT_2 etc.

I’d like the price to be selected dynamically according to each $LOT_MALT_x… I know i’m getting close but i can’t figure out how to properly do it. Here is a fragment of my total malt price formula (it’s kinda brute force with 15 if loops but i’ll optimize someday) :


prix=0
année=0
#if column $MASSE_MALT_1 is not empty
if $MASSE_MALT_1:
#extract the year from the corresponding serial number
  year=MID($LOT_MALT_1, 3, 2)
#calculate the MALT_1 subtotal price and add it to the "prix" variable
  prix+=$MASSE_MALT_1*getattr(rec, "$NOM_MALT_1.PRIX_AU_KILO_"+année)
 
 #repeat the above for each $MASSE_MALT_x :
if $MASSE_MALT_2:
...
...
...
return prix
4

Can you create a shareable version of your document that I could take a look at?

It’s not totally clear to me how the rows of the MALTS table line up with the recipe table. And I’m a bit confused about what values should be totaled in the final price column of the recipe table.

5

I’ll do that in a few days and reply back, thanks for the quick answer !

6

Vacations are over, back to work :expressionless:
I made a very light copy of my work document with just what is needed to understand the case.

7

Hum, your link brings us to a Yuno Host login page. Maybe share your document thru Grist sharing mechanism instead.

8

Not sure how to do that, what i did here was click on the share icon on the top right grist UI, manage users, make public and pasted the given link… Is there an other way i don’t know ?