AppSheet Balance

[erin0809]

Good day! 

I'm a beginner in using AppSheet, and I would just like to get some help because I've been stuck in this error for quite some time now. My goal is to make a cashless canteen POS, where the cashier can add the customers' orders and also add balance to their cards. The "add order" feature is consistently doing great, and its inputs are transferred quickly in the google sheets. But for the "add balance" feature, I was having a rough time since I can't seem to transfer the new balance from the tab, "Student Balance", onto the "Students" tab, so that it can be updated anytime the student loads their card. This feature is so important, but I can't seem to fix it. Attached below are my screenshots about my app. The first pic is the students tab while the second is the student balance tab. The last two pics are my appsheet, where it can be seen that there are some errors (in actions and automation) due to us trying to fix it. This can be fixed quickly, as we can just delete those new added actions and automation. But going back, please help me out for my thesis T__T It would mean a lot to us. Thank you very much!  sum balance is a virtual column, but i can add a column to itself in the sheets

[erin0809 · Accepted solution]

hi! one of my groupmates actually solved it just yesterday by using the sumif formula in google sheets, but this problem is related also to the "balance left" feature of our application. for example, if we order an item that costs 150 currency, and the student has 200 currency, they would only have 50 currency left. all's well in recording the orders and in the calculations of the "balance left" in the google sheets, but our conflict with it is that we can't figure out how we can transfer and update this "balance left" data from each specific student, to the students tab, where the records of every students' balance can be seen :(( we tried exploring the subtraction formulas of google sheets, but we just can't see the right formula for us, so we are trying to utilize the automation feature in appsheet now T__T but if i were to answer the question you asked,  it's neither of them and we want to have Student Balance[Sum Balance] = Students[Balance]. But now, we would want Customers[Balance Left] = Students[Balance], so that it could be updated every time a student orders. provided below are some screenshots; the first one is the customers tab, where the balance left can be seen, and the second screenshot is the students tab, where the balance column can be seen. our goal is to have the data of the balance left from a specific student, go to the balance column in the students tab T__T thank you so much for replying!

[lynchk21]

Do you want:

1. Student Balance[Current Balance] = Students[Current Balance]  or
2. Student Balance[Sum Balance] = Students[Current Balance] ?

It seems like you could just total all [Student No].[Amount In] - [Student No].[Order Cost] for each student to get their real time balance. 

[erin0809 · Accepted solution]

hi! one of my groupmates actually solved it just yesterday by using the sumif formula in google sheets, but this problem is related also to the "balance left" feature of our application. for example, if we order an item that costs 150 currency, and the student has 200 currency, they would only have 50 currency left. all's well in recording the orders and in the calculations of the "balance left" in the google sheets, but our conflict with it is that we can't figure out how we can transfer and update this "balance left" data from each specific student, to the students tab, where the records of every students' balance can be seen :(( we tried exploring the subtraction formulas of google sheets, but we just can't see the right formula for us, so we are trying to utilize the automation feature in appsheet now T__T but if i were to answer the question you asked,  it's neither of them and we want to have Student Balance[Sum Balance] = Students[Balance]. But now, we would want Customers[Balance Left] = Students[Balance], so that it could be updated every time a student orders. provided below are some screenshots; the first one is the customers tab, where the balance left can be seen, and the second screenshot is the students tab, where the balance column can be seen. our goal is to have the data of the balance left from a specific student, go to the balance column in the students tab T__T thank you so much for replying!

[lynchk21]

You can use a bot to update Students[Balance]  to equal [Balance Left] each time a record is added to Orders table. Since you have a lot of tables we can't see, I can't provide much detailed info. 

If you want to accomplish the same thing in Google Sheets, you should be able to use a vlookup paired with a query in the Balance column of the Students table to get the most recent record. Something like =IFERROR(VLOOKUP(A2, QUERY('Customers'!1:1001, "SELECT D, J WHERE J <> '' ORDER BY B DESC", 1), 2, FALSE), "")

[erin0809]

hi !! this is so helpful, thank you so much! i wanted to accomplish it on google sheets, and while i was experimenting with vlookup, i got your notif and used the formula that you provided :)) i get the logic, but it doesn't seem to work? it got an error, and that's why it only showed blank. does the "iferror" formula only work in texts and not numerical characters? here are my other tabs also. again, thank you so much for this !!

[lynchk21]

If you remove the IFERROR() function, what error are you getting?

[erin0809]

heree

[lynchk21]

I would put the QUERY portion into a blank tab and see what results you are getting. If you are getting the correct results back from the QUERY, then you know there is a problem with the data types. If you are not getting the correct values back, you know there is an issue with the QUERY. 

[erin0809]

It appears that the data types are wrong in this case as the query returns the values needed, but I can't seem to solve it still? Very much sorry, I'm not a techy person. I sent you a direct message, if that may help 

[lynchk21 · Accepted solution]

The QUERY was wrong, as the syntax J <> "" does not work, but needs to be IS NOT NULL instead. Here is the corrected formula:

• =VLOOKUP(A2, QUERY(Customers!1:1001, "SELECT D, J WHERE NOT J IS NULL ORDER BY B DESC", 1)

[erin0809]

Thank you so much again! ☺️