Help needed - Computed keys limitation?

[Rory_Forde]

I am new to Appsheet.

On the face of it, Appsheet looks promising - I had been trying to work with PowerApps but the licencing issue were just too much to get on top of. Hence my look into Appsheet.

However I appear to have come to an early (and disappointing) impasse. It appears to me (I hope I am wrong) that Appsheet cannot handle any relationships in Google sheets other than the most basic (Order / Item type). When it comes to composite keys it doesn’t appear to know what to do.

So I am wondering if Appsheet is a tool for anything more than a very basic application. I really don’t want to spend a lot of time going down (another) rabbit hole to discover that it leads nowhere.

I have an application, that can be modelled quite easily using a traditional relational DB, and in Google Sheets with a bit of imagination. I would like some opinions as to whether I am wasting my time on this platform or if I am just missing something fundamental. I am willing to engage Appsheet experts to get me over this hump if necessary.

Thanks,

Roryf

[Suvrutt_Gurjar]

Hi @Rory_Forde ,

Please take a look at the following AppSheet help articles just in case you have not

https://help.appsheet.com/en/articles/1023086-what-is-a-key

https://help.appsheet.com/en/articles/961426-references-between-tables

[SkrOYC]

Rory_Forde:

So I am wondering if Appsheet is a tool for anything more than a very basic application

No

Rory_Forde:

using a traditional relational DB,

AppSheet kinda follows the rules for RDB but not 100%. Some things like 1-to-1 need workarounds

Rory_Forde:

Appsheet cannot handle any relationships in Google sheets other than the most basic (Order / Item type). When it comes to composite keys it doesn’t appear to know what to do.

I’m lost here. Can you explain it to us a little bit better?
‘Relationships in Google sheets’ doesn’t sound like a thing. What do you mean?
Composite keys… you need sequential keys or something?

[SkrOYC]

SkrOYC:

Composite keys… you need sequential keys or something?

beginnersbook.com – 19 Apr 15

Composite key in DBMS

Definition of Composite key: A key that has more than one attributes is known as composite key. It is also known as compound key. Note: Any key such as

Est. reading time: 1 minute

Gotcha

You can, although I strongly suggest you not to do it.
The best way of having a key for each row is by leaving a real column for that purpose of text type and UNIQUEID() Initial Value. Also leave it hidden (Show: off) because it doesn’t have any meaninfull info rather than a pseudo unique string.

help.appsheet.com

What is a Key?

Learn the purpose of keys and why they’re necessary.

Natural Multi-Column Keys

Your table may contain two or more fields that together containing values uniquely identifying each row. For example, a Vehicle table may contain a State field and a LicensePlateNumber field that together uniquely identify each row. A multi-column key is slightly more unwieldy than a single column key, but it makes an excellent key.

Natural Computed Keys

Your table may contain fields that can be combined to yield a unique computed key value. The row’s computed key value must remain constant for the life of the row.

You specify the key computation expression in the key field’s app formula property.

The Editor examines this expression to ensure that it yields the same result over time, and displays an error if it does not. For example, an app formula that includes the current date or time might yield different results over time, so it would be prohibited in a key field’s app formula

[Suvrutt_Gurjar]

Thank you @SkrOYC for taking out related specific extracts from the respective articles. You are correct that composite key equivalent “Computed key” is described in the article on keys. The reason shared that article with @Rory_Forde

Also from his following statement, it appears that he could take a look at the article on referencing and then rephrase his questions better.

Rory_Forde:

Appsheet cannot handle any relationships in Google sheets other than the most basic (Order / Item type).

[SkrOYC]

Yep. It all made sense once I got it. Your links are what he needs.
The whole documentation to be fair is the best place to start from my POV

[Rory_Forde]

Thanks for all of the replies…

If I may illustrate with a simple fictional example. In a single medical practise a Doctor has many patients. But if you have many medical practises to handle a patient may also have many doctors (GP, Ophthalmologist, etc…). In this case you would need a composite key (PatientID & DoctorID) to identify what doctor the patient was visiting.

Can Appsheet handle this? If so where is the best place to look for tutorials on using Appsheet with more complex data structures.

Thanks,

RoryF

[SkrOYC]

I still don’t get it but it may be because I don’t understand the usage of “Composite Key”. For me it seems easy from an AppSheet “creator” perspective to use a table where I just select the patient and the doctors assigned to him.

Rory_Forde:

a Doctor has many patients

Sure, you could have this as Doctor (Parent) and Patiens (Children)

Rory_Forde:

But if you have many medical practises to handle a patient may also have many doctors (GP, Ophthalmologist, etc…)

This is where my english skills might be betraying me but if I get it right you mean that there are a lot of different treatments and, consecuencially, doctors related to a single Patient. This sounds like many-to-many.

Rory_Forde:

In this case you would need a composite key (PatientID & DoctorID) to identify what doctor the patient was visiting.

Why is it needed? It seems like just a table where you could select both is enough. Again, Key is just a hidden column that has no purpose rather than uniqueness in the context of a table, from my POV.

Rory_Forde:

Can Appsheet handle this?

Your need for a composite key or the usecase? Seems like it can handle both.

Can you come up with a different example? Also it could be very helpfull if you tell us why the composite key is so important in your case

[Suvrutt_Gurjar]

You are correct @SkrOYC. @Rory_Forde seems to be looking for many to many table relationships. These can be implemented with a bridge table in Appsheet. Below are many useful posts on implementing many to many table relationships in Appsheet.
https://community.appsheet.com/search?expanded=true&q=Bridge%20table%20%20%20%23ask

[SkrOYC]

You could even do it inside AppSheet with EnumList basetype Ref on both tables, no need for the third one

[Suvrutt_Gurjar]

Correct.

[Rory_Forde]

That sounds interesting - I will have to look that up…

[Suvrutt_Gurjar]

SkrOYC:

You could even do it inside AppSheet with EnumList basetype Ref on both tables, no need for the third one

Hi @SkrOYC ,

Just thought of adding a few points for discussion’s sake. Even though technically one could use Enumlist with base type as a reference in both the tables without bridge table, as per my understanding, it may not be a practical solution in most cases. Of course, there may be a few use cases for that.

Consider Orders and Products. If the two are referenced to each other simply via enumlist with base type reference, it will be difficult to pull in other product attributes such as product name, product price etc. in Order table. Also summing up the order value will be difficult. So I believe in most cases a bridge table such as Order details that references both Orders and Products table makes sense from user experience perspective also.

Please take a look at the below post to understand the challenges in dereferencing column values from the parent in the case of enumlist with base type as reference.

Just checking..Is there yet a way to pull a single REF item from a list and dereference it? Questions

Many times we have lists of REF items (such as for an EnumList/REF column). Occasionally, we want to pull an item from that list and access some of the other row attributes. Currently, we need to pull that item from the list and then use it for matching in a SELECT() function. For example: ANY(SELECT(Table[Desired Attribute 1], [ID] = INDEX([REF Enum List], 1))) ANY(SELECT(Table[Desired Attribute 2], [ID] = INDEX([REF Enum List], 1))) ANY(SELECT(Table[Desired Attribute 3], [ID] = INDEX([RE…

Edit: Some edits to the post description.

[Steve]

Rory_Forde:

In this case you would need a composite key (PatientID & DoctorID) to identify what doctor the patient was visiting.

Can Appsheet handle this?

Yes. Just mark both of those columns as key and AppSheet will automatically add a column named _ComputedKey that will use the two component columns to produce the composite key.

[Joseph_Seddik]

Rory_Forde:

If I may illustrate with a simple fictional example. In a single medical practise a Doctor has many patients. But if you have many medical practises to handle a patient may also have many doctors (GP, Ophthalmologist, etc…). In this case you would need a composite key (PatientID & DoctorID) to identify what doctor the patient was visiting.

Away from AppSheet, you might want to build a better database in any case. Your problem is not with the composite key (that fellow members have shown you how to achieve), but rather your data structure is weak.

A Doctor is a Doctor
A Patient is a Patient
A Visit is neither a Doctor nor a Patient

To build on the same fictional example, you want to register visits, then I’d say you need at least the following tables:

• A Doctors Table
• A Patients Table
• A Therapies Table, this will contain the names of the Doctor and the Patient, and may be a “Condition/Syndrome” and “Status”
• A Visits Table, the visits here will be related each to a Therapy, and would contain date and time.

That’s the way you do it.

Also, you can go further, you can create: Medication, Tests, Exercises, Diets, etc. All are tables that should be related to Therapy.

And so on.

[SkrOYC]

I might be wrong but “Composite Key” is a concept that may be needed outside AppSheet and completely useless or not needed inside of AppSheet.
Maybe outside AppSheet using a RDB you need to have meaninfull Keys and that kind of stuff but here a Key is just a single column where a random and hopefully unique value is asigned when the row is created to be able to ref to it later or just keep it unique in the context of a table.
Hope this helps!

BTW, this is the default expression that is used on AppSheet for uniqueness:

help.appsheet.com

UNIQUEID()

Pseudo-unique ID

[SkrOYC]

After learning a little bit more, the short answer…

Yes

AppSheet can do all of the above

For reference

[Rory_Forde]

Thank you all for your replies and your time…

The take away for me from this discussion is that Appsheet will do what I want to do - I just have to figure it out.

RoryF