App table referencing logic (ENUMLIST, one-to-many, many-to-one)

Hi everybody,

I’m experimenting with Appsheet and I’m building a simple app that has the following functionality:

  • The main view shows a list of (organisational) Capabilities (e.g. “Strategic Management”)
  • Every capability consists of supporting 1. Data component(s), 2. Process(es), 3. Application(s) and 4. Team(s).
  • I created a ‘Capabilities’ table + 4 tables for the four ‘supporting’ elements.
  • All 5 tables have some added columns that provide extra information.
  • The four tables with the supporting elements, all have a ‘Capabilities’ column.
  • Appsheet automatically creates a link/reference between the different tables
  • This way, when I go to the Capability view, it shows all the related data, process, team and application elements.
  • I can add new Capabilities from the Capability View and I can add new supporting elements from the other views.

My problem:

  • It is possible that a certain Data component/Process/Team/Application supports multiple Capabilities.
  • Example for Applications: Appsheet allows me to add multiple Applications under 1 Capability (‘many-to-one?’), but I don’t understand how I can create/edit an Application and have it support 2 different Capabilities (‘one-to-many?’)? So that when I go to the Capability view, the same Application shows under 2 different Capabilities, without having to ‘duplicate’ the Application.
  • Ideal situation: I would like to be able to use ENUMLIST when I create/edit an Application, and be able to pick all Capabilities the Application supports, and if the Capability does not exist yet, that I also am able to add a NEW Capability. So basically: ENUMLIST+ADD based on Referenced table/column.

I hope someone can point me in the right direction and my apologies if I didn’t use the correct terminology! Still learning :slight_smile: !

All the best and thanks in advance!

I will share my understanding and suggestion. There may be better approaches with enumlist as a reference that I am unaware of.

I believe you could explore a bridge table to establish a many to many relationships between Capabilities and its four support attributes ( Data Components, Teams, Applications and Processes).

The bridge table may be called say CapabilitiesSupports. This table will have references to all 5 tables namely Capabilities, Teams, Applications, Processes, and Data Components. The reference to the Capabilities table in this table can be made" Required" so that each CapabilitiesSupport record necessarily has a Capabilities reference and other 4 optional references. In general, there could be many records in the bridge table related to one capability and multiple support attributes.

Please note this may need more rigorous testing for all edge cases before you could implement it but I believe it is worth a look.

So the detail view of a CapabilitiesSupport record could look like below. The record below has 4 support attribute references for capability “Strategic management”

This is another record from the bridge table CapabilitiesSupport for the capability of Strategic management.

This is detail view of Capabilities table where those two Capabilities Support records appear as related records.

The below is detail view of Data Componentes detail view where the related record from
CapabilitiesSupport table shows up.

Hope this helps. Again, please wait for any easier option a community colleague may share and if you implement this , please test on a smaller POC :slight_smile:

3 Likes

Hi Suvrutt,

Thank you so much for your elaborate reply! I’m going to try and implement this. Can you post screenshots of the table structure (and/or formulas) you used to make this work? Thanks in advance!

Kind regards,

Kay

Hi @Bibaboy10 ,

After replying to your post a week ago, today morning, a few hours ago I deleted the app that I created to demonstrate the functionality, since it was purely created to respond to you with proper images and identical table names. Nonetheless, there are not significant formulas but principle of bridge table that is vital concept in this case.

As I have mentioned, the central concept is a bridge table that connects Capabilities and the 4 attributes. The bridge table concept is also demonstrated in the sample app referred below.

I will try to compare the tables in the sample app with the tables in your requirement so that you get perspective. Hope this helps.

https://www.appsheet.com/samples/An-app-for-managing-customers-products-and-orders?appGuidString=83be58ea-1901-4a6d-ac9c-38c7c555d8cd

2 Likes

Hi Suvrutt,

I think I made it work! Thank you so much for your quick replies and elaborate explanations.

All the best,

Kay

2 Likes

Hi Kay,

Thank you for the update. Nice to know you made it work. May I suggest you to share if you used the same principle of a bridge table or any other approach for the benefit of other community members who may read through this post thread in future?

1 Like