i need help solving a problem in my appsheet that is been bugging my for 2 days straight

[marvaddi]

so I'm transferring all my inventory that is in my google sheet to the AppSheet. I heard about AppSheet and wanted to give it a try. It's my second day learning this software and I have some questions. I've attached some photos to better clarify my problem.

A little BACKGROUND:

so here is the scenario,  I have two sheets "Inventory" and "Orcan". And anytime I have  new products, I would like to put them into the "inventory" sheet. (i buy my products from amazon and sell them on facebook and Instagram, so basically i download my report from amazon website and copy paste the data into the "inventory" sheet. however, i do add some columns to that history report but that's irrelevant). after i copy paste from my amazon history report into my "inventory" sheet, ONLY two parameters are changed, and that are columns [TYPE] and [selling price]. [TYPE] is a list of 4 strings, "sold", "selling on fb", "selling on ig", and "Kept". And [selling price] is just a price that i set to sell the product. This is all for "inventory" sheet, it is just there to add stuff into manually.

Now the "orcan" sheet is where all the action happens. this is where I would like to use the app to input my updates. not to input products, but only updates to those products, because I already copy/paste the new products into the "inventory" sheet. so the updates are as follows: 

I made the  "Orcan_Form" to ask the user(me) to scan the product. or input the [QR num] manually. Then the form will pull up that product automatically based on that [QR num] value. Then it will ask for [quantity sold], [type0], and [sold price]. Now, [sold price] is the real price sold (sometimes i mark my stuff to be $100 but end up selling for $90).[type0] is an important column, this is Enum, it asks the user whether the product is "sold" or "kept", (sometimes the product doesn't sell so I keep it). and finally [quantity sold] is the quantity sold duhh. once those are inputted it will go to the "orcan" sheet on my google sheets. Also, very important to note, I have programmed the columns [title], [image], and [purchased] (in "orcan" sheet) to fetch the values of that product from columns [title0] [image1] and [purchased] from the "inventory" sheet. and I did that by setting the orcan [qr num] as a reference to "inventory" as shown on the table.

NOWWWW HERE IS WHERE IM am GOING CRAZYYY

i want to update my "inventory" sheet based on the input I provide for the "orcan" sheet.  And the conditions are as follows:

ONE-once [quantity sold] is inputted from the app and listed in "orcan" sheet, I want that value for that product to be synced with column [Quantity sold] in "inventory sheets". 

TWO- the input i provided for [Type0] in "orcan" should be updated in column [TYPE] in "inventory". if it is "sold" it should update it for [TYPE] in "inventory", But this is only valid in one condition. When the value of [Quantity to sell] in "inventory" is equal to ZERO. if not, leave as is. so if the quantity to sell is 4 and i inputted "sold" in the orcan_form, this should not update the column [TYPE] in inventory because i still have more quantities, but if it is 0 then it should display "sold". and thissss is what i cant doooooo, i tried a lot of functions to no avail.
FYI, i have a formula that is set for [Quantity to sell]. [Quantity to sell] = [Qty on the floor ] - [Quantity sold].

THREE - if the inputted value in column [Type0] in "orcan" is "kept", then update that value in the column [TYPE] in "inventory" and also mark the [selling price] to be equal to negative[purchased]. (because if i kept the item i just lost the money that i paid for it)

So these are the only three conditions left that I can't wrap my head around! I'm thinking its gotta do something with my reference column or some other minor things but i really need help to solve this problem. The crazy thing is, i was able to fetch the values of a product from columns [Title],[image],[purchased] from the "inventory" sheet to "orcan" sheet(based on the scanned [qr num] from the app) with no problem. BUT I CAN NOT DO THE OTHER WAY AROUND!!!!. if you have a solution please help

FYI, i included some screen shots of my sheets. Note that i couldn't take a screen shot on the appsheet website because the list is too big and i would be required to scroll hence i couldn't take the screen shot. but i did convert that table that is on the appsheet website into a table in google sheet. Also, some columns in my "inventory" sheet are hidden to avoid clusters. hope that helps