Automatic table sorce change

[aTrotes]

Hello, I would like to know how to do the following:
I have a form in which there is a Combobox where I can select countries, France, Italy, Portugal.
In the spreadsheet I have 1 sheet for France, another for Italy and another for Portugal and I want that when the user makes his choice, in Column Data [Type:] Ref change [Source Table] to the table of the selection.

[TeeSee1]

My first thought would be - because I do not believe that you cannot change column definitions on the fly - that you combine the three tables into one with a column identifying which country each row belongs to.

That way you can change how you process the data depending on the choice of the Combobox.

If you share more details, then, you might get more suggestions in the community.

[aTrotes]

Yes, this  is my actual option, but I have many files in this sheet and I add many countrys with several locations. I think is a best way have separate sheets but I don´t Know how to get.

[graham_howe]

There are two options here I think:

1. Assuming you are using Google Sheets, then you could easily create an additional sheet and use the following formula to automatically include data from the other sheets.

=query({France!A:E;Italy!A:E;Portugal!A:E}, "select * where Col1 is not null", 0)

There would only be one table created in AppSheet. If you ensure that each country sheet has a column which includes the country name, then this can be used to filter the data in AppSheet. 

2. Load all country tables into AppSheet and then use an expression which switches between select statements based upon the selected country. Something like this:

SWITCH(
  [SelectedCountry],
  "France", FranceData[Key],
  "Italy", ItalyData[Key],
  "Portugal", PortugalData[Key]
)

[aTrotes]

Thanks Graham, but I think this solution does not solve my problem

I put another example:

I have Combobox with diferent selection to enter data about this place location: lake, mountain, river, monument, Protected natural areas  etc

When I select Protected natural areas , I have a database, and I need to uplodad another combo to select the right. 

When I select "monument" I have not a Database and I input all data

[Koichi_Tsuji]

Dont use Spreadsheet formula with appsheet as a best practise.

[Koichi_Tsuji]

You are faiing to organize the data tables. Stays with single table for all the countries data you have . That is starting point for you.

https://help.appsheet.com/en/collections/377937-data

[graham_howe]

Absolutely correct, the best approach is to have a single data table. However I have had a situation in the past (also country based as it happens) where we needed to have people from different countries manage the data in the spreadsheet. In that situation it was better to have a tab per country and then use protection to determine who could edit what. However to pull all data into another system (actually Data Studio rather than AppSheet, but the same principle) I used the approach described in option 1.

I only added option 2 in case there was a desire to keep all design in AppSheet, but personally I think it is a more complex approach and less true to what I understand as the principles behind AppSheet.

[Koichi_Tsuji]

Solution is super easy, not to let your user touch the spreadsheet, directly. Instead let them edit and update data through the appsheet.  

[Koichi_Tsuji]

In case we fail to structure the schema for an app, then we keep failing afterwards.  The data schema is the most important aspect with the appsheet.

As app owenr (creator) we never allow user to access to the data source (mostly spreadsheet) directly as a part of our governance.

[graham_howe]

I've only been working on AppSheet for a few weeks, but yes that is absolutely my approach. However there are situations where AppSheet needs to present data which is being collected through another mechanism. This only really works if the 'external' data is well structured, good quality and is read only from AppSheet perspective. As soon as there is the possibility that data can be updated by more than one system or process there will be problems.

But taking an extreme example, if I want my app to capture service visits at customer sites, I will need access to a list of customers. If those customers are actually managed in my SAP system, then I am not going to want to move customer master data management across to AppSheet!

[Koichi_Tsuji]

"Union" tables, could be only one possible solution, if you could not touch the data schema upon, so that AppSheet works properly for you.

If you have different tables with the same schema, just union them and let the AppSheet to read as a single table.

If not possible (to review the schema), Appsheet is not able to give you full potential performance. Off course, AppSheet should not be blamed for it.

[TeeSee1]

One possible approach for multiple users to maintain data is use edit-if condition based on a control table.

That table would have two columns, one country, the other a list of users who can edit this country.

Using this check table, enter in the edit if (or even the behavior of actions) something like 

IN(USEREMAIL(), SELECT(control_table[allowed user list], [country] = [_THISROW].[country]).

May not work in every use case but should work for this particular case of maintaining a country dependent master table.

Just an idea.

[aTrotes]

Thanks for all the answeres.

I know it's a difficult question
It seems that it is something that the creators should implement

I am currently trying to understand how splitting a file into multiple parts from Appsheet works.