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:
Problem:
Questions:
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.
What do you mean by that?
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,
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,
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,
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.
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.
JobStatus
JobStatus |
---|
Active |
Pending |
Closed |
Inspection Type
Inspection Type |
---|
Condition Assesment |
Concrete casting |
etc… |
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.
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… |
Unless you’re wanting the ability to expand the values of a list, it’s not necessary to build out a support table. (IMHO)
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)
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…
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?
Like this:
SELECT(
ReferenceTable[ReferenceValue],
("ClientType" = [ReferenceItem])
)
See also:
Steve,
Thank you for your response.
Will try and report back.
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:
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).
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:
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.
Why duplicate the ClientName and ClientCode on the Jobs layer, you’ve already got that information stored on the Client level.
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…)
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
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)
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!
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.
Skr,
The “Manually Generating UNIQUEID()” is VERY helpful normalizing the data we have.
Thank you very much.
Sure, that’s how I do it.
I’m glad you found it helpfull
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…
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:
PS: I already read the following links:(and they are not clear about this).
I think you should take a look at the documentation in the help tutorials, with half an hour of reading, you already have it!
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?
Lynn,
Thank you for your prompt response.
Here is the an image of
Any ideas?
@Felix.P_Tong
What happens when you click on test,do you get the expected result?
User | Count |
---|---|
26 | |
25 | |
25 | |
23 | |
23 |