Adding and removing inventory based on form input

Termy · 05-05-2021 05:20 AM

Hello,

TDLR; I need help with forms and user input to help me add and remove inventory depending on there input

I’m working on a inventory management app for the company i work for and I’ve hit a snag. How the app works currently is that there us a form that people fill out to add or remove inventory. Then after it logs the entry. In said form they had to select the item, date, and how much they are adding or removing. However, that last part is a bit weird, in order for a person to remove inventory they have to use negative numbers in the text box. I was wondering if there was a way to use kind of like an IF statement? What I mean is IF (Adding/Removing) = adding then it would add the amount that the user inputted into the amount column. The same goes for removing expect it subtracts from the inventory. Then in the log there it indicates which happened. Any help would be much help.

Marc_Dillon

Yes, you can do it that way.

What I’ve done in the past is have 3 columns:

quantity input
add/remove Enum selection
calculate actualy quantity adjustment

Set validation for the first column to not allow input of negative values.
For the third column, you use an App Formula to calculate the actual adjustment amount:

IF(
  [add/remove] = "remove" ,
  -1.0 * [qty] ,
  [qty]
)

Then you use the third column for all of your summation expressions down the road.

This is just one option among many.

Termy

Alright ill try it, but then how can that change the amount of current inventory that i have. The columns in using in this form are:

I’ve also don’t think I quite understand what you are meaning either. I have a column for the amount. I followed your instructions but I don’t know what I’m doing wrong. When I tested it and it worked but didn’t change the inventory total.

would something like IF( [add/remove] = "remove" , -1.0 * [qty] , SELECT( Inventory[Amount], ([Item ID] = [_THISROW].[Item ID]) ) ) work.

Sorry I guess I just need to learn more.

Marc_Dillon

The Table that is referenced by the [Item ID] column is where the actual inventory amounts are kept, correct? Is this app based on one of the inventory sample apps? Presumably there is a a virtual column in that Items Table that is using a SUM(SELECT(...)) expression. It would have been referring to the [Amount] previously, now it just needs to refer to the [New Amount] column instead. Also, I highly recommend making [New Amount] a real column, not virtual.

Termy

yes its based off the inventory sample app. Then to make it a real column do i have to go to the sheet that it’s linked to correct? yes to the first question. Whats the main different between a real column and a virtual one?

Marc_Dillon

Yes. Add the column, then regenerate from the Appsheet editor

Main difference? Real column is in the backend database, Virtual is just in the App. The reason I suggest making it real is because it will soak up a lot of computing resources upon every sync for no reason if you keep it as virtual.
https://community.appsheet.com/search?q=real%20column%20vs%20virtual

Termy

Thanks for all the help! I’m still having issues removing the inventory. I added all you’ve suggested and the new amount is a negative number but the log and inventory still adds to it rather then removing it.

Marc_Dillon

Did you check that?

Termy

Presumably there is a a virtual column in that Items Table that is using a SUM(SELECT(...)) expression. It would have been referring to the [Amount] previously, now it just needs to refer to the [New Amount] column instead.

I changed it to [New Amount] but it just changed the amount to whatever the user put it rather than removing one. also I’m still kind of confused on what you mean too.

Marc_Dillon

Screenshots please.

Termy

let me know if you need more

Marc_Dillon

Termy

ok ill try it

Termy

thank you. i was just being dumb and not understanding. thank you for your patients