Is it possible to have a Table without a Key column?

[FTong]

I am trying to set a Table where the columns are standard values to be selected (in other tables).

Ej: Billing Rate, JobStatus, ClientType, etc.

The idea is:

1. Have a single table where the variables/options are kept.
2. Make it easier to maintain options over time.

Problem:

1. Appsheet does not let me have a table without a key.
2. Even if I try to turn it off, Appsheet creates a _RowNumber Column and designates it as the key.

Questions:

1. Is it possible to have a Table without a column selected as a key.
2. If not, is it possible to have a table to hold values, options in a different way?

[SkrOYC]

Felix.P_Tong:

Is it possible to have a Table without a column selected as a key.

No. It’s the base of any table. The bare minimum columns you need are 2:
A Key and the value you need.
You could have just 1 and make it Key but that’s really risky.

Felix.P_Tong:

is it possible to have a table to hold values, options in a different way

What do you mean by that?

[Steve]

Current_User (Slice) - How to conform your app around WHO is using the app Tips & Tricks ?

One of the core basic functions, included in just about EVERY app I make, is the ability for the app to know WHO is using the app. If the app knows who’s using it, then I can easily control many different aspects of the app: Add/edit/delete permissions How data should be filtered What views are shown What buttons, or Actions, are visible Which workflows should fire off Which columns should be shown or editable etc. Requirements To accomplish this functionality in your app, you need the follo…

[FTong]

SK,

Thank you for your prompt reply.

(Disclaimer: I am an old engineer “dinosaur” used to the basic spreadsheet that is new to Appsheet. )

Typically when I set up a spreadsheet,

1. I create a tab where to keep the options for cells I wanted to control the value of.
2. Then named ranges are used to force the control values in other tabs/cells as required.

I am trying to set up a similar process using Appsheet.
Right now I am trying to use a “ReferenceTable” to hold the control values.
Then, in the other tables columns that I want to control the input values, use “Enum List → Data Validity → Valid If” and point it to the column in the “ReferenceTable” that holds the values I want to limit input to.

EJ-Variable#1: JobStatus: (Active, Pending, Closed)
Ej-Variable#3: Inspection Type: (Condition Assesment, Concrete casting, etc…)
Ej-Variable#2: Inspector: (Ft, Sb, Ap, etc…)

In principle, I think it should work.

However, the table Key prevents this from working because if I try to control more than one value in a table, the first variable I pick up from the “Reference Table”, forces any subsequent variables using Enum List Validation to the same row as the first variable.

I do not know how to circumvent this.

So, by “Other Way” I mean an Apsheet compatible way to hold control values.

Any ideas or suggestions would be appreciated.

Thanks.

[FTong]

Steve,

Thank you for your prompt reply.

(Disclaimer: I am an old engineer “dinosaur” used to the basic spreadsheet that is new to Appsheet. )

Typically when I set up a spreadsheet,

1. I create a tab where to keep the options for cells I wanted to control the value of.
2. Then named ranges are used to force the control values in other tabs/cells as required.

I am trying to set up a similar process using Appsheet.
Right now I am trying to use a “ReferenceTable” to hold the control values.
Then, in the other tables columns that I want to control the input values, use “Enum List → Data Validity → Valid If” and point it to the column in the “ReferenceTable” that holds the values I want to limit input to.

EJ-Variable#1: JobStatus: (Active, Pending, Closed)
Ej-Variable#3: Inspection Type: (Condition Assesment, Concrete casting, etc…)
Ej-Variable#2: Inspector: (Ft, Sb, Ap, etc…)

In principle, I think it should work.

However, the table Key prevents this from working because if I try to control more than one value in a table, the first variable I pick up from the “Reference Table”, forces any subsequent variables using Enum List Validation to the same row as the first variable.

I do not know how to circumvent this.

So, by “Other Way” I mean an Apsheet compatible way to hold control values.

Any ideas or suggestions would be appreciated.

Thanks.

[Steve]

Although AppSheet can use spreadsheets to store data, AppSheet is not a spreadsheet, so you shouldn’t expect it to act like one. AppSheet treats spreadsheets more like a database.

In a database world, one approach would have each column of your reference table be a separate database table. To translate that to a spreadsheet, each column of your reference table would be its own worksheet of the workbook. In your app, you’d have a separate app table for each worksheet.

Worksheet JobStatus

JobStatus
Active
Pending
Closed

Worksheet Inspection Type

Inspection Type
Condition Assesment
Concrete casting
etc…

Worksheet Inspector

Inspector
Ft
Sb
Ap
etc…

Another legitimate approach in the database world would be to have a single database table (spreadsheet workbook) that has two columns: one that identifies the control set, the other that contains one value in that control set.

Worksheet ReferenceTable

Control	Value
JobStatus	Active
JobStatus	Pending
JobStatus	Closed
Inspection Type	Condition Assesment
Inspection Type	Concrete casting
Inspection Type	etc…
Inspector	Ft
Inspector	Sb
Inspector	Ap
Inspector	etc…

[MultiTech]

Steve:

Although AppSheet can use spreadsheets to store data, AppSheet is not a spreadsheet

AppSheet is a User-Interface provider for spreadsheets

• Meaning: AppSheet merely provides the UX and logic stuff… the actual data storage is all handled elsewhere
  • AppSheet is the visuals, the buttons, the charts/graphs - along with data validation elements as well.

---

In regards to “Support” tables…

Unless you’re wanting the ability to expand the values of a list, it’s not necessary to build out a support table. (IMHO)

For example

JobStatus
Active
Pending
Closed

• I wouldn’t create a support table for this, since the status values are very unlikely to change

• For these, I would just hard-code them into the Enum options (or use a valid if)

Its only when I need that support table to be able to grow that I actually include an additional table.

• Otherwise you can end up with a bunch of “extra” tables, that really aren’t that functional.

[FTong]

Mat/Steve:

Thank you for your prompt response.

The reason I am trying to set up control sheets is that I do not know yet what are the control points for what I am doing (Aka- I am not sure if what I am doing is right). So a table format makes it a lot simpler for me to keep track of my control values.

From your explanation, now I understand that in Appsheet is better to store values vertically than horizontally in the tables. Like in the quote below. I can sort the table based on the initial control point and that would take care of “visually” keep it simpler at this point for me.

Once more- THANK YOU for your help…

Steve:

Worksheet ReferenceTable> > | Control | Value |> | - | - |> | JobStatus | Active |> | JobStatus | Pending |> | JobStatus | Closed |> | Inspection Type | Condition Assesment |> | Inspection Type | Concrete casting |> | Inspection Type | etc… |> | Inspector | Ft |> | Inspector | Sb |> | Inspector | Ap |> | Inspector | etc… |

[FTong]

Mat/Steve:

I consolidated the ReferenceTable from 10+ to 2 Columns (+ID Column).
(ID / ReferenceItem / ReferenceValue)

Now, how can I reference the values in the table to limit the input options in other tables?

Before, to limit the Client Types I would:
Data Validity->Valid If-> ReferenceTable[Client_Type] ← Very spreadsheet like…

Now: ReferenceTable Looks like (see below), how can I limit the options to the values in ReferenceValue column?

[FTong]

I have two tables: CLIENTS and JOBS.

In Table “CLIENTS” I have among other columns:

IDClient = Key
ClientName = Virtual Column (First Name + Last Name) / Label.
ClientCode = TXT

In Table “JOBS” I have among other columns:
IDJob = Key
IDClient = Ref
ClientName = TXT
ClientCode = TXT

I am trying to set up a form to collect the jobs information in the JOBS table.

Question:
1- How can I copy CLIENT.Name to JOBS.ClientName when selecting IDClient in the form?
2- Same for ClientCode.

[anonymous]

I think you should take a look at the documentation in the help tutorials, with half an hour of reading, you already have it!

[MultiTech]

Why duplicate the ClientName and ClientCode on the Jobs layer, you’ve already got that information stored on the Client level.

• If you need the value for some formulation, or to display it in an email or something… use a de-reference to get that value then - no need to duplicate like that.
• All that does is add bloat to your app, and possibly stale data.

[Steve]

Felix.P_Tong:

Now, how can I reference the values in the table to limit the input options in other tables?

Like this:

SELECT(
  ReferenceTable[ReferenceValue],
  ("ClientType" = [ReferenceItem])
)

See also:

[FTong]

Mat:

Once more thank you for your prompt response. (It is VERY appreciated…)

I am trying to create a couple of Appsheets apps for me (and two other people -dinosaurs- I work with), I thought prudent to make it as seamless as possible. (So - i hope - the less changes is introduced in their work flow, the less friction I would receive). I have to confess that we are no spring chickens anymore and data entry mistakes are starting to be noticed.

The reason for duplicating data is that other spreadsheets currently in use also read data from the same tables/spreadsheet I am trying to use to set up appsheet with. (Note: they only read client data and do not write to it).

Right now I am using a copy of the spreadsheets data to work with Appsheet. Hopefully, when I get it to work as needed, I can just redirect the spreadsheet to the original data an start making the transition then. (they can use the spreadsheet to input info or Appsheet. (I know appsheet should be al lot easier to use once the app is set up properly).

On the other hand, if I do not fill the field (or remove it from the table) there is a lot of spaghetti code I may have to get back into.

PS: Please excuse the long explanation. But, I thought it would give some perspective to my questions that may not seem Appsheet like standard use…)

[FTong]

Steve,

Thank you for your response.

Will try and report back.

[SkrOYC]

I think it won’t be possible to work with the same data inside and outside AppSheet unless is just for Data Analysis.
You should just work with one copy in order to understand how to migrate to AppSheet in the long term and then do it completely.
To ‘retrofit’ or ‘normalize’ your data is not that easy when you have a lot of rows and you were not used to Keys and relationship between tables.
I’ve done it and it’s not easy. But, if you get how things work inside AppSheet, I it’ll be easier the more you try

[FTong]

Skr,

Thank you for your timely comment.

I am exactly at that point:
a) old data (that is not pristine).
b) Appsheet that makes "all corners square ".

I am trying to work with what I have. This is requiring a lot of quirks and adjustments that are taking a toll on the time needed to get Appsheet to work.

But “normalizing” the data seems to be required after all.

(Nice term: Normalizing Data… - Thanks)

[SkrOYC]

Hi @Felix.P_Tong
I think this will be very usefull for you.

The last will help you to get the same ID that is created using UNIQUEID() inside AppSheet but directly to your excel/sheets
Then you can point tables using Lookup and other expressions.
I can’t help you that much since my main language is spanish and I write formulas in that language on Excel. Also, it will require some excel/sheets skill that I hope you have.
In general, add a Key column to every table you have today and then mention the Key from one table on another (that will require another column) in order to generate a relationship between them
Also, I have no credit about the term!

[FTong]

Skr,

Thank you for your prompt response to my message. (It is appreciated…)

Also, thank you for the reference information provided.

I browsed through it and found it “eye-opening”.

I come from the spaghetti code spreadsheet world, and Appsheet seems to be a way to sort things out (still using spreadsheets) while keeping control of the data.

Thank you again for your help.

[FTong]

Skr,

The “Manually Generating UNIQUEID()” is VERY helpful normalizing the data we have.

Thank you very much.