Barcode scanner and Importrange

[Alpesh_Sanghvi]

Hi,

I have a master spreadsheet where all the products and related barcodes are stored. This is my distributor’s app. Next, I have customer apps where each customer has its own data, but the products, barcodes, and prices are the same as the distribution app. If I use importrange to sync the data, I can not set the barcode column as key and if the barcode column is not set as key then the scanner function doesn’t work. I have to have separate data sheet for customers because there are additional columns there such as their own selling price which each customer would have a different price.
What would be a work around for this. As I have to copy and paste to each customer’s data every time we add new product or change prices or images on the distributor’s spreadsheet

[Steve]

Alpesh_Sanghvi:

I can not set the barcode column as key

Why?

Alpesh_Sanghvi:

if the barcode column is not set as key then the scanner function doesn’t work.

In what way doesn’t it work?

Alpesh_Sanghvi:

As I have to copy and paste to each customer’s data every time we add new product or change prices or images on the distributor’s spreadsheet

Sounds to me like your data should be restructured. Ideally, customer data and product data shouldn’t be mixed together.

help.appsheet.com

Data: The Essentials

A high-level overview of concepts for organizing and using data in AppSheet.

[Alpesh_Sanghvi]

Steve:

Alpesh_Sanghvi:

I can not set the barcode column as key

Why?

I get the following error:
The key column ‘Barcode’ of table ‘Products’ is not a legal key because it contains a Formula. We chose a new key for ‘Products’.MORE INFO
Table Products must have a key column. We assigned ‘_RowNumber’ as the default key, but you should choose a better one.MORE INFO

Steve:

Alpesh_Sanghvi:

if the barcode column is not set as key then the scanner function doesn’t work.

In what way doesn’t it work?

Error:
Column Name ‘Barcode’ in Schema ‘Products_Schema’ cannot be scanned (not Barcode scannable) because it contains a formula or AppFormula.

The formula is importrange(“url_of_spreadsheet”,“Products!A2:G”)
This imports all Product data from main Product spreadsheet to Customer Product spreadsheet. In the customer Product spreadsheet (table) there is an additional column (Unit Price) which the customers can add his/her own selling price. For different customers to be able to set their own prices I have set it up this way.

The Data documentation helped to understand better, but not enough to restructure the data. I need each customer to be able to set their own price but have all other Products details the same as set by me.

[Alpesh_Sanghvi · Accepted solution]

Ok figured it out.

Data > Columns > Products > Edit Barcode > Spreadsheet Formula. Here you delete everything and leave it blank. Save and all is working, even the importrange formula in the spreadsheet