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 ?

9

Can you tell me a bit more about that ? I cannot find anything…

10

Hello @Enthouane

I was meaning to reply, but I dropped the ball.
Thanks for the gentle nudge.

What you wrote two weeks ago should have worked. Rather than messing around with public access on what seems to be your businesses’ server, I suggest you do this instead:

  • share icon on the top right grist UI > Download…
    • download a copy, without history (2nd choice) will be lighter
    • this will get you a .grist file.
  • go to https://docs.getgrist.com/ and connect to your account
  • at the top left, green button [Add New] > Import Document
  • import your .grist file.

Once imported, start overt with the sharing process you used last time, but from this Grist cloud hosted copy.

Let me know how it goes.

m

P.S.: Ça a quelque chose d’absurde de correspondre en anglais avec des “cousins” outre-Atlantique qui parlent la même langue que soi. Mais pour le bien du plus grand nombre (sur ce forum), faut ce qu’il faut. :wink: