Re: Product name on Sales List

Jabbs · 04-02-2020 03:38 AM

I use Sales Form with the option of scanning. This is how it looks https://i.imgur.com/wJGMIkG.jpg
It has columns like here https://i.imgur.com/omUJkQk.jpg
Also I have Sales List which has same columns as Sales Form.

I need an additional column on the Sales List. This column should match the name of the scanned product [product name]. How do I add it as a Virtual Column?
What value should I use?
I know that I must first add this column to Sales Form.
Help me

Kirk_Masden

Hi Jabbs! Welcome to the community!

Do you mean that you have another table called “product” and this table has a column called “product name”? If so, how is it connected to the sales list? It’s difficult to answer without more information.

By the way, in the future, you can put the kinds of images you shared this time directly in your posts. To do so, just tap on the tag that looks like a picture:

Jabbs

This is stock app. I have table called Products and I have column name PRODUKT which has name of each product in it for example “znaczek”. Also I have this column in my google sheet so this is not a Virtual Column.
I can upload only 1 image.

Jabbs

I would like to create the same column “Produkt” (product name) in the Sales table. Right now my Sales List looks like this:

Jabbs

I need to add same column like in PRODUCTS table and I do not know how to do it. Can I use VC?
If yes what value should I add??

Kirk_Masden

Thanks for following up. When you say “same column” do you mean that there is a one-to-one relationship between the rows in the two tables?

For example, in a spreadsheet, if you have Sheet1 and Sheet2 and there is a one-to-one relationship between the two sheets, we could bring column D in Sheet1 into Sheet2 as follows:

='Sheet1'!D1
='Sheet1'!D2
='Sheet1'!D3

etc.

This is easy to understand but AppSheet doesn’t work this way. In AppSheet you need to connect tables by their “keys”:

Do you know what your keys are in each of your tables?

Jabbs

hello
this is my Products table (all products in my stock are here)
each product NUMBER corresponds to PRODUCTS name

Jabbs

and this is my SALES table.

All my sales are here but I need extra additional column with PRODUCT name here.
And question is how can I do that?
All Sales are done with scanning. I do not put any PRODUCT name into SALES FORM. I do not want to do it manually.

Is it possible to edit SALES form in Appsheet. So when I scan product barcode and product name will appear automatically?

hru

Seems like you need to add below expression in the App_Formula of Product column:

	ANY(
	    SELECT(ProductsTable[Numer],
		[_THISROW].Numer = [Numer]
	    )
    )

Please ensure names are matching with your table name and column name.

hru

In AppSheet.

Jabbs

App sheet says that this formula is correct. But for example:

Product with number 4M0949101 is always PRODUCT X.

So I should do correct expression for each number -> name ?

hru

What if you change Znaczek to Numer ? and test the expression.

Jabbs

Column with this expression is called “New Virtual Column”
but like you see nothing is there

Jabbs

and this is exact expression for this column:

Bjarne_Allerup

Let me guess.

The only thing you want is to also have the product name in Sales Table, look up from the Product Table - is this what you want?

I’m looking for something like this myself.

Jabbs

yes this is what I want. I know that I can do it manually with “Enum” value in Sales Table column but I would like to do it automatically somehow

Bjarne_Allerup

I think you should look at this de-reference Table sample.

https://www.appsheet.com/samples/A-basic-demo-of-table-references?appGuidString=6ffad040-b04a-4325-a...

Jabbs

YES YES YES
this is what I was looking for!
Works perfectly. Thank you @Bjarne_Allerup

Kirk_Masden

I’m glad you found what you were looking for!

hru

Maybe I understood incorrectly.

Please confirm below:
If [Numer] is a Ref to source table Product, then you can just do:

[Numer].[Produkt]

the Produkt is the name of the column of Product table where you want it to be shown in the Sales Table.