The result of the following equation SUM ( S...

(UNITED SKATEBOARDS) #1

The result of the following equation

SUM ( SELECT (WAREHOUSE[PRODUCT NAME], [QUANTITY] = [_THISROW].[PRODUCT NAME]))

Does not sum on its own with the inventory table when adding new items in the quantities section.

I noticed that when I press to modify in one row in the inventory table and re-save without any modification, the equation works and shows the result of the sum of the quantities entered in the warehouse

Is there a better way to show the sum quantities in the warehouse without opening the registry, modifying it and closing it?

Attached image example of the tables used and the location of the equation used

(I add quantities to the table, the program doesn’t sum automatically. I have to open register, save and close…so that program update quantities)

(Levent KULAÇOĞLU) #2

SUM(SELECT (WAREHOUSE[QUANTITY], [PRODUCT NAME] = [_THISROW].[PRODUCT NAME]))

(UNITED SKATEBOARDS) #3

@Levent_KULACOGLU My apologies,

This error in code is only in the post. I actually used the function properly. And the function works fine ( no errors )

SUM(SELECT (WAREHOUSE[QUANTITY], [PRODUCT NAME] = [_THISROW].[PRODUCT NAME]))

My problem is that the function does not

SUM

automaticaly

(I add quantities to the table, the program doesn’t sum automatically. I have to open register, save and close…so that program update quantities)

(Levent KULAÇOĞLU) #4

@UNITED_SKATEBOARDS what @Fernando_Lopez saying that use the expression =[VirtualColumnName] in the AppFormula property of your [Quantity] column

(Fernando López) #5

You need combine both.

Virtual column for show automatically updated data like @Aleksi_Alkio

suggested and the “real” column with the APPFORMULA to save the data to your spreadsheet

(Syed Faheemuddin) #6

@UNITED_SKATEBOARDS

You need to write a spreadsheet formula. ( You will find it at the last in edit column definition) It will update automatically in the excel but you will need to write a different formula according to excel.

(UNITED SKATEBOARDS) #7

SUM.mp4 drive.google.com

(Syed Faheemuddin) #8

@UNITED_SKATEBOARDS

Use Sumif

in spreadsheet formula in quantity column of Stock table

(UNITED SKATEBOARDS) #9

@Sdfaheemuddin

Thank you very much i tried " SUMIF "

in Spreadsheet formula

now the program is doing as required.

P.S.

I had to modify a section in your formula to make it run properly.

From

SUMIF(‘WAREHOUSE’!B:B,B2,‘WAREHOUSE’!A:A)

TO

SUMIF(‘WAREHOUSE’!B[2]:B[2],RC[-2],‘WAREHOUSE’!A[4]:A[4])

Also Thank You All For Help

:slight_smile:

(Syed Faheemuddin) #10

Happy to know it worked.

(Levent KULAÇOĞLU) #11

Please keep in mind that, when your data size grows up, it will take time for the spreadsheet formula to calculate the result, hence you may not be able to see the calculation result immediately after the sync. Therefore, using a VC and then appending it as an expression in the AppFormula property of the physical column will yield to a better result in terms of efficiency.

(Fernando López) #12

Agree with @Levent_KULACOGLU and also you keep the logic of your app

just in appsheet, it’s better for the app maintenance

(Levent KULAÇOĞLU) #13

Also there is this: in general gSheet has a limitation of 2M cells for data handling. This 2M limit is considered for text only and provided you have formulas (especially reference formulas or complex expressions), this 2M limit drastically reduces. What you have done, basically creates a spreadsheet function in each cell when you create a record. Therefore I humbly suggest to re-consider your approach.

(Levent KULAÇOĞLU) #14

Is this expression in a Virtual Column?

(UNITED SKATEBOARDS) #15

@Levent_KULACOGLU

thank for the information

bro. it is very useful.

can you give me example on how you can append VC to AppFormula as expression.

my programming is beginner and I dont understand some technical words. maybe I already do it and I dont know. can you give me example.

(UNITED SKATEBOARDS) #16

@Levent_KULACOGLU

Yes this is an example to show u my problem. I have the original program do you need to see it ?

(Aleksi Alkio) #17

What Mr Levent is trying to explain… your Quantity column needs to be a virtual column (Stocks table). At this moment it’s a normal column and you need to open & save if you want to see the status correctly.

(UNITED SKATEBOARDS) #18

can you plz tell me how you make virtual column. can you give me example.

I have the program. I want to share it with you. so you can view it for reference. how can I share it.

(Aleksi Alkio) #19

Open the correct table from Data > Columns and in the bottom of that column structure you will see a “+ Virtual column”. When you click that text, it will add you a virtual column.

(UNITED SKATEBOARDS) #20

@Aleksi_Alkio

I tried this virtual column with my code. It works fine and shows the updated value at the same time.

Problem with virtual column, it will not write on the Excel sheet.

The resulting value is shown on program/ application sheet, but not stored on Excel file.

How can make it so that the results are also stored on Excel sheet