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

 

 

 

 

 

  

 

[TeeSee1]

From the Inventories table, you can access the columns of the related row in orcan as

[related orcans][here orcan's column].

The expression above returns a list, so depending on the type of the column, you need an additional function wrapping the expression - for instance for Quantity Sold...

SUM([related orcans][Quantity Sold]).

Since inventories and orcan has 1 to 1 relationship, this should be fine.

For a text fields, use ANY(......).

Having said this, implementing this as well as the rest of your requirements based on App Formula in the inventories do not work or not preferable because

1) updates in orcan will not update values in inventories automatically, you have to edit the relevant row in inventories

2) required expressions in AppFormula will be messy because you need to consider the initial condition where a corresponding orcan row does not exist yet and also after one is created, you need to accommodate various conditions.

So I would depend on the technique based on INPUT function that I demoed in this Q&A

There are other sources you can reference for more information such as

https://help.appsheet.com/en/articles/5398619-dynamic-inputs-for-data-change-actions

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Using-INPUT-to-update-another-table-using-a-valu...

Yes, you still need to write expressions to meet your conditions but this I believe will be cleaner and better.

Last but not least.

Having a one to one relationship between the two tables means you cannot record more than one sale against a row in the inventories. Is this OK? You never sell a batch of procured items in multiple sales, different customers, dates, selling prices...

[marvaddi]

Thanks for your genuine feedback teesee1. As I have said I'm still learning, this is my third day and I need more time to get familiar with expressions. 

you mentioned above that depending on my column, I should wrap my expression with some function. I kind of did what you said and it keeps giving me the error "Column 'Related Orcans' is used in a SELECT or list dereference expression and should be a List/EnumList of Refs".

so initially, when I was creating my app, I set my [qr num] in my "orcan" sheet as the reference. And that reference is linked to the "inventory" sheet.  from there, I was able to fetch any column from "inventory" sheet to "orcan" sheet simply by using the expression [qr num].['desired coulmn']. 

For example, if I want the value for [image1] from "inventory" to appear on my column [image] in the "orcan" sheet. I would simply go to "orcan" columns, from there I would type the expression -  [qr num].[Image 1] - this would return the value in column [image 1] in "inventory" to column [image] in "orcan". I was able to do that with no trouble. Same thing with the title column. i just wrote the expression - [qr num].[title] - and the same thing with my purchased column, I used - [qr num].[purchased] - I was able to fetch the columns of " inventory" to "orcan" with no problem

now, I'm trying to do it the other way as well, I want to write an expression in any column that belongs to "orcan", and fetch those values down to "inventory" columns.

for example, what kind of expression would I type into the formula section for the column [TYPE] in the "inventory" sheet. So that i can get the values of column [type0]. 

 if i want to display the value of column [type0] "orcan" to column [TYPE] "inventory". I would simply go to the [TYPE] column in "inventory" and i would use the expression - [related orcans][Type0], but this returned an error "Column 'related orcans' is used in a SELECT or list dereference expression and should be a List/EnumList of Refs"

mind you, that [TYPE] and [type0] are both set to Enum. and when you said to wrap them in a function, i didn't know which to choose because they are both enum. you said if it was text wrap the expression in ANY(...) so i changed them both to TEXT and i went to write down the expression ANY([related orcans][Type0]). BUT I ALSO GET THE SAME ERROR.  plus I'm confused about the period. previously i wrote - [qr num].[Image 1] - (and it worked fine), and now you suggested that i write [related orcans][here orcan's column] with no period in between, what difference does that make. 

 my question to you is, In the "orcan" sheet, the column [qr num] is set to reference to "inventory" which means it's related to "orcan". Can I assume that column [related orcans] in the "inventory" sheet is also a column referenced to "orcan" sheet? if so, why do my expression not work?! what kind of a function should i use to wrap around the expression in order for this to work. 

I really appreciate your time.

[TeeSee1]

It certainly takes some time to get used the quirks of AppSheet. It's been 6 months for me and I am still learning.😁

https://www.appsheet.com/portfolio/3401559

See sales tracker for what I have done.

This is by no means complete (lots of logic still to be implemented) but just to show you how you dereference things to get to values in other tables via ref cols. (Be sure to 'edit' inventory to reflect any changes in orcan.

You can read this Dereference article for the difference between period/no-period expressions.

To pull data from inventory into orcan, it is the 'regular' deref. The other way is a list deref. That is why you need different expression formats.

note: I was mistaken a bit when I said use SUM() for quantity and ANY() for text.

Since you have a 1 to 1, then ANY() should work for either.

I still prefer to use actions to reflect changes in orcan back to inventory.

[marvaddi]

THANK YOU SO MUCH for taking the time and actually making an app to better explain it to me Teesee! I did run the app, but it still would not work I check all your formulas and the conditions, but it still won't update the values from the "orcan" back to inventory.

I did make a video replying to your feedback and I explained what my thought process is, I tried to keep it as short as I could, i don't want to take more of your time. But I would be grateful if you watched it and tell me how I would approach this problem. Once again, Thank you!

[TeeSee1]

I wrote

"Be sure to 'edit' inventory to reflect any changes in orcan"

You are NOT doing this.

Just go to the orange record in inventory, hit edit and save and the data should be updated.

This is what is required when you use App Formula.

This is why I said, using actions makes more sense because values in orcan will be reflected in inventory automatically but would involve more setup...

[marvaddi]

Ahaaaaaaa!!!! I see now! okay so this solution works but it would be very daunting to go into each "fruit" and  and edit/save it would take forever. So you are saying it is best to add new actions eh? I will try that method too. I will keep you updated if it works. 

[marvaddi]

hey teesee! boring updates here! still have not figured it out. I watched this video on linktoform(). and I thought this expression is helpful. in his video, when he edited the item, the results went to both tables "receiving(inbound" and "products".  the inputs created a new row in the sheet "receiving(inbound", and the updates reflected on sheet "products". This is exactly what I wanted.. that my inputs would go to a new row "orcan" while the updates went to "inventory" I tried this expression(screenshot provided), it worked on in "orcan" I was able to create a new row there. but it did nothing to "inventory" it did not update it at all. What I'm I missing here?

[marvaddi]

i did see the updates you made to the app, I like the results and I will proceed with this solution. But is there a way to make "Call Update Invenotry" action appear on my orcan sheet above the plus sign? it will be awesome if it could update all the products on one click rather than going to each product and updating it. Thank though teesee 🙂

[TeeSee1]

The action is a row level action. therefore cannot do mass update.

As I asked in another post, what is your data entry sequence?

That should determine how you navigate within the app.

EDITED;

Howerver, by setting this on the behavior of the orcan Form, the update will be 'automatic' (no need to press the button, but when you save the record, the action will be fired)

[TeeSee1 · Accepted solution]

Hmm, as I already mentioned, App Formula does not get updated unless the row is edited (even in the official documentation).

.So not really sure what magic the guy in the video is using..

I will dig around a bit.

Meanwhile, maybe someone else might show us the secret trick..

Meanwhile, I am a bit confused how you intend to use the app, especially data entry sequence. I understand inventory manages items you procure but seems like you are also using it to enter transactions.

All the product related seems to be kept in inventory and copying most of the infor into orcan is redundant and unnecessary.

[marvaddi]

Hey Teesee so I did what you said and actually, the Event Actions under the behavior of orcan_form is already set to auto. But I still have to press click the 'call update inventory" in order to update it. when I tested your app initially, I would go to orcan, choose the item(qr_code) I want to update, then fill the rest of the form [type0], [qty_sold], [price_sold]. and I hit save, this will get me back to orcan view, which then I have to click on the item I just added THEN click the "call update inventory". So I just did a minor change to that by changing the 'finish view' under orcan forum to be set to 'orcan_details'. That way when I hit save it will get my back to the orcan_detal page so that I could click the "call update inventory". i still prefer if I can mass update the whole "inventory" in one click though. and speaking of that I understand that the action you created is a row level action. Im a  bit confused when you asked me about my data entry sequence. And you are also confused on how this app is being used. here is a brief explanation.

to clarify, Orcan a name of a friend of mine, she will be doing all the selling for me. However, she does not speak much English. So I'm trying to make the app as easy as possible for her. all she gotta do is (scan the item, input the type, input qty/price sold). so I made that tab in her name so she knows where to go to sell the item. but I also want her to see the inventory tab, just so she can get familiar with the details of the product.

Now, "inventory" is there for me to look at the products that are selling to IG, selling to FB, kept, or sold. This sheet is categorized by the products' DATE RECEIVED. Now I agree that "All the product related seems to be kept in inventory and copying most of the infor into orcan is redundant and unnecessary." Orcan on the other hand, I want that sheet to list the items that are either sold or kept ONLY. and is categorized by  DATE SOLD/KEPT. This sheet also helps me do my weekly expense/profit sheet. I don't know how else I can categorize my inventory sheet by the date received and also date sold. So that's why I separated them. and by doing this,  I also want the values of "orcan" to reflect back to "inventory" so that I could see my TOTAL expence/profit along with other stuff. in the future, I will be making a monitor tab to see all of the data. In the meantime I just want to take it easy and see how the app goes, I'm still learning and hopefully, I will achieve my desires in making a solid inventory app.

Also I forgot to mention, i list my item on facebook shops. and I do that by something called feed. so basically facebook shops is scheduled once a week to fetch my "inventory" sheet and update the items automatically.