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?

[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.

[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]

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?

[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:

help.appsheet.com

SELECT()

Gather column values from row in table or slice

help.appsheet.com

SORT()

Sorted list items

[FTong]

Steve,

Thank you for your response.

Will try and report back.

[FTong]

Steve,

This is Felix again… I am an oldtimer engineer (some of the younger engineers in the office call me and my other senior engineer friends “the dinosaurs”). As you can imagine me and my dinosaur colleagues work on spreadsheets most of our professional life. I stumbled upon app sheets about a month ago and I think, with a little work, this can change the way a significant amount of engineering work is done - particularly workflow issues. (Unfortunately, most of my younger colleagues do not share my opinion - at least at this time).

Before I continue, I like to thank you, Mat, and others that have been very gracious and shared some of your knowledge (and time) with me over the last couple of weeks.

I set my mind to proving the younger crowd wrong. So I am learning Appsheet in my spare time. However, I am having difficulties understanding how to refer to data in Appsheet formulas. (And I suspect is related to unconscious bias from my spreadsheet background.)

For example, sometimes I think I am referring to a single cell and I get a list of matching values. Other times I think I am referring to a list of values and I get a single (or none) value. (See Example 2 below).

Examples:

1. Ref Tables:

Ref Tables is what initially got me interested in Appsheets.
The way I understand this, if a table is referenced into another, all the columns of the first table are available for calculations (and modification) , when entering data on the second table.
However, how to address the values of the first table when entering data in the second table turned out to be (and still is) a little fuzzy… as I suspect it does not match my bias towards spreadsheet formula logic.

Say I have an app with two tables: Jobs and Orders. (there is a JobNumber column in the Jobs table)
Say the Jobs table, is referenced in Orders Table. (there is also a JobNumber column in the Orders table - duplication for data dinosaur readability purposes).

If I want to copy the JobNumber from the Jobs table to the Orders table I found -from your and others’ comments- that I have to use a Select() statement instead of just entering Jobs[JobNumber] in the Orders Initial Values formula. (This, in my opinion, is a different paradigm from the traditional spreadsheet logic).

2. Time Calculations:

This one I have not yet figured out. Say for example I want to calculate a future delivery date. In my case, I would add the number of working days to Today() and get my future date. However, this does not seem to be the way it works in Appsheet.

In my case, I have two columns DeliveryDate and EstimatedTimeOfCompletion.

EstimatedTimeOfCompletion = user defined days required to complete work.
EstimatedTimeOfCompletion = NUMBER Type Column
EstimatedTimeOfCompletion(Initial Value) = 21

DeliveryDate = Estimated time of delivery of complete work.
DeliveryDate = DATE Type Column.
DeliveryDate(Initial Value) = Today() + EstimatedTimeOfCompletion

However, I have tried the different “spreadsheet like” formula combinations as INITIAL VALUE for the DeliveryDate column and can not get the results I want:

a) Today()+1 <-Returns Tomorrow’s date
b) Today()+EstimatedTimeOfCompletion <-Returns Today’s date.
c) Today()+[EstimatedTimeOfCompletion] <-Returns Today’s date.
d) Today()+EstimatedTimeOfCompletion+1 <-Returns Tomorrows date.

So, at this time I think, the issue I am coming across is more likely a User (me) issue.

Is there an article that addresses this basic concept topic of how to address values in the columns? (Perhaps geared towards people coming from spreadsheets worlds, like me?)

These are some of the articles I have already read looking for this information:

help.appsheet.com

Date and Time Expressions

Incorporate dates and times into the behavior of your app.

help.appsheet.com

DATE()

Date from Date, DateTime, or Time

[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.

[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.

[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…)

[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.

help.appsheet.com

Data: The Essentials

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

help.appsheet.com

Tables: The Essentials

Key insights to add tables in your app.

help.appsheet.com

Manually Generating UNIQUEID() Key Values

How to manually generate UNIQUEID() key values.

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!

en.wikipedia.org

Database normalization

Database normalization is the process of structuring a database, usually a relational database, in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity. It was first proposed by Edgar F. Codd as part of his relational model. Normalization entails organizing the columns (attributes) and tables (relations) of a database to ensure that their dependencies are properly enforced by database integrity constraints. It is accomplished by app...

[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.

[SkrOYC]

Sure, that’s how I do it.
I’m glad you found it helpfull

[FTong]

Skr,

It just happen that I also hablo español…

The “Data Normalization” tip turned out to be a real technical term…

Naively I though it was Appsheet slang…

[FTong]

Hello Matt,

This is Felix, your friendly dinosaur engineer learning Appsheet again…

Now I am having a problem (actually many, but this seems simple and bugs me that I can not figure it out).

Problem: calculating a future date in a Date column.
(See partial image of Table below).

I want to define the EstimatedTimeOfCompletion using an “INITIAL VALUE” formula as follows:

Today()+TimeAllowanceDays

I like the user (my friend Alberto - 78 yrs old - one of the other dinosaurs in the office) to select in an Appsheet form how many days he wants to add to the initial value to complete the work using the TimeAllowanceDays column and have Appsheet calculate the expected delivery date for him.

Note: Actually I like him to add the “Working Days-Monday to Friday” and not “Calendar Days” but I have not found a simple way to do this in Appsheet. So “calendar days” is a compromise so he can see what AppSheet can do - for now.

It seems simple enough, however, I can not get this work as I only get ‘Todays date’ and not ‘Today date + 21 days’.

I have tried the following formula combinations as INITIAL VALUE for EstimatedTimeOfCompletion and get the results shown:

a) Today()+1 <-Returns Tomorrow’s date
b) Today()+TimeAllowanceDays <-Returns Today’s date.
c) Today()+[TimeAllowanceDays] <-Returns Today’s date.
d) Today()+TimeAllowanceDays+1 <-Returns Tomorrows date.

Questions:

1. What is the correct way to call the value of another field into a formula.
2. Any other tips that may help would be appreciated.

PS: I already read the following links:(and they are not clear about this).

help.appsheet.com

Date and Time Expressions

Incorporate dates and times into the behavior of your app.

help.appsheet.com

DATE()

Date from Date, DateTime, or Time

[stilllifemuseum]

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

help.appsheet.com

AppSheet Help Center

[Lynn]

Hi @Felix.P_Tong
Could you please show a screen shot of your exact formula. I dont have time to study your whole post but is TimeAllowanceDays a column?

[FTong]

Lynn,

Thank you for your prompt response.

Here is the an image of

1. the Table
2. the equation.

Any ideas?

[Lynn]

@Felix.P_Tong
What happens when you click on test,do you get the expected result?