Retrieving Information from Another dataset

[Collectiumbro]

Formula Presentation for Retrieving Information from Another dataset

Background:

We are developing a collector platform with a price list using AppSheet. While we initially considered using Google Sheets, we encountered limitations with each dataset, which can only hold 15,000 rows. As we already have 2,000 static rows of information, we require an alternative solution to create lists of collectibles and enable collectors to cross-identify their items.

Question:

We seek assistance on creating AppSheet formulas to retrieve the correct price or other information and fetch data from one dataset to another.

Datasets and Spreadsheets:

The dataset/spreadsheet is named "PNS Pris" and contains the following tabs:

• PNS 2019
• PNS 2020
• PNS 2021
• PNS 2022
• PNS 2023
• PNS 2024

All tabs have a header row. Here is a list of the headers along with an explanation of the content type:

• (NSNR) (Catalog number)
• Title (Unique product name)
• Good (Condition grade with price for the current year, e.g., 58Kr)
• Very Good (Condition grade with price for the current year, e.g., 58Kr)
• Fine (Condition grade with price for the current year, e.g., 58Kr)
• Very Fine (Condition grade with price for the current year, e.g., 58Kr)
• Extremely Fine (Condition grade with price for the current year, e.g., 58Kr)
• About Uncirculated (Condition grade with price for the current year, e.g., 58Kr)
• Uncirculated (Condition grade with price for the current year, e.g., 58Kr)
• Choice Uncirculated (Condition grade with price for the current year, e.g., 58Kr)
• Gem Uncirculated (Condition grade with price for the current year, e.g., 58Kr)
• Superb Gem Uncirculated (Condition grade with price for the current year, e.g., 58Kr)

Request:

Frequent requests involve finding a specific year, which corresponds to PNS 2019 to 2024. Subsequently, the title and a quality grade should match the request.

Other Tasks:

I have several other tasks that I need to accomplish, but I'm not entirely familiar with the process.

How Can You Help Me?

Can you guide me on creating AppSheet formulas to find the correct price or other information and demonstrate how to retrieve data from one dataset to another?

Thank you for your valuable assistance.

[AleksiAlkio]

Firstly.. how many rows do you think you will have from one year, is it this 2000 rows per year? Why do you think the sheet can have max 15k rows when using gSheet? In generally it would be better to have all prises in one table as then your formulas would be much simpler.

[Marc_Dillon]

In addition to Aleksi's comment, your data structure would be better suited as a 4-column table with NSNR,Title,ConditionGrade,Price, instead of the headers that you describe. So you'd have 10 rows for every one row you currently have, one row per ConditionGrade. This will make lookup expressions much easier.

[Collectiumbro]

I have read that gsheet has a limit of 15,000 rows! is it wrong? I think I will get about 50,000 rows for each year. possibly more. have read that you can expand the AppSheet to 200,000 rows.. I agree that it is best to have everything in the same sheet. it will be much easier. but I have several prices to be matched from different spreadsheets and databases.

[Marc_Dillon]

Yes that's wrong. GSheets can hold a whole lot more than 15k rows. Where did you read that?

[Collectiumbro]

Sorry. I'did read wrong.. 

Google Spreadsheets are extremely powerful and convert very well from Excel, but they do have some limitations:

Up to 10 million cells for spreadsheets that are created in or converted to Google Sheets

40,000 new rows at a time

Maximum number of columns of 18,278 columns

Number of Tabs: 200 sheets per workbook

GoogleFinance formulas: 1,000 GoogleFinance formulas

ImportRange formulas: 50 cross-workbook reference formulas

ImportData, ImportHtml, ImportFeed, or ImportXml formulas: 50 functions for external data

Maximum string length is 50,000 characters

For the latest information on Google Drive's supported file types and sizes, see Files you can store in Google Drive.

[AleksiAlkio]

Yes, the Cell limit is 10 million, but the real limit with gSheet so the app still works quickly enough is somewhere 500..1000k cells if the app is structured properly. It also depends how many tables you have. If you can backup old data yearly, it could be possible. But if you need to have access to all data from the beginning, you should think to use real database with the app.

[Collectiumbro]

Do you know how this function? Whats limit and possibility for this function?

[Marc_Dillon]

Doesn't it say right there in your screenshot what that option does?