Multiply two columns in a table

Mr Unfreeze

Hello,
How may I multiply (for each row) two columns within the same table in a database, and create a new column with the result?

Thank you

Mr Unfreeze

It worked ! Thank you Pawel

Pawel Magdanski

Hi,

If you really need to have the multiplication in additional column then you need to create additional column for you result in table manually and possibly update it via endpoint whenever those two values you are multiplying are updated.

So for example if you are getting those 2 values as an input from your front-end, before creating/updating record, first create a variable which use "multiply" filter and do the math. Then you can save all three of your values in database.

On the other hand, result of multiplication of two values you already have in your table is derivative and multipling two values is something that Xano is doing really fast, so it might be a better idea to do this on the fly in endpoints whenever you need this value on the front-end.

Mr Unfreeze

2: Hi Pawel,

Thank you for your answer. Indeed at a later stage this data will come from the front-end and populate the database. I tried to create a variable through a function but can't "get" the data to do the multiplication.

1. Function get all record from the "financial data" table, which I narrowed to the 2 variables I want to multiply, in this example "trip_weekend_frequency" by Trip_weekend_amount". (btw I want to do a for each loop so that I do this for each row in my table)
2. I created a multiply variable to multiply the two above-mentioned fields. The problem is that in the "value" field, I do not see these 2 fields so I can't multiply one by the other.
   
   Do you know how to make them appear ?
   
   Thank you Pawel .
   

Pawel Magdanski

Ok,

So in order to access a particular field in the object (in this case your Financial_data_1) you need to use dot notation. so to get Trip_weekend_Amount you need to have a value: Financial_data_1.Trip_weekend_Amount (this will be a list of the values for all records in your query, to do this for one at the time use loop).

Alternative is to use "get" filter then as a value you are using Financial_data_1, then "get" filter and Trip_weekend_Amount. The advantage here is that you can set a value if a particular path to the field does not exists which is very useful in many situations.

Hope that helped

Mr Unfreeze

Hi Pawel,

Thank you. So I tried with the get filter but it does not collect the data from the "Trip_weekend_Amount" field (result is "null for 3 rows). Did I do something wrong?
Thank you

Pawel Magdanski

Can you use stop&debug to show finacial data query?

Mr Unfreeze

Sure: I made other attempts today. Do these screenshots help you?

Pawel Magdanski

In your multiply variable after multiply filter you need to yet again use either dot notation or get filter like in the value field. So Financial_data_1.Trip_weekend_frequency or Financial_data_1 → get → Trip_weekend_frequency

And if you want this to work you need to use the loop, because at the moment you are trying to do calculation not using two values, but two arrays.

Mr Unfreeze

Hi Pawel,

Thank you! Unfortunately, still does not work…

The for each loop gives the same result

Mr Unfreeze

I can't delete my previous post so I create a new one.

I kept working on it and it seems I've made some progress now. It returns me the result of 1 multiplication but not the full list with others, although it calculates them. How may I get the list ?

Pawel Magdanski

Great,

I think the easiest way would be to create a new vield in your financial data variable by doing this.

update variable → item

existing variable: item.result

value: multiply

Mr Unfreeze

Good morning Pawel,
Now, it works as I wanted. I can display the list as shown in the first screenshot.
I created a "Budget_weekend" field in my table and I would like to fill in the values calculated (1400, 200, and 2500) for their respective ID in this table.

I tried the "edit record" manipulation but so far I get errors. Do you know what's wrong? Thank you

Pawel Magdanski

Well,

as an error suggests you put your filed name budget weekend in a field value where the id of your record should be. Try item.id

mjreis

I still cannot believe how complex this simple operation is. Probably better to learn coding and write code for a back end! I have a list of orders and another list of orderItems. In order items I have Quantity and Price. To calculate the order total, I need to calculate the orderItem total. I still cannot find a way to multiply them. Any summary anywhere?

Liz Anaya

Hi @mjreis! 👋

If you are looking to simply multiply 2 values together, you can use the multiply math filter.

Let me know if this is helpful! 😊