Solved: Recommendations for automation for creating multip... - Page 2

gcor71 · 08-16-2024 12:44 AM

I have three tables:
1. MEMBERS - a list of names and details.
2. GROUP - a list of groups that members are part of (1:1).
3. DISTANCE - a list of distances between members and group, for each member there will be many rows, each with a distance to a group. This "is part of" the MEMBERS table.

The intention is to create - for each member - a list of distances to groups, then using that list to find nearest groups and show within the member record etc.

Automations I have tried (including using solutions proposed by @Steve in his excellent post ) all, eventually, fail to execute. Some run then fail, others don't start.

I'd appreciate suggestions, ideas and hopefully example apps showing how the desired behaviour can a be met. I'm not so concerned about fixing the current approach, more interested finding a way forward even if a different approach will work better.

Following examples are intended to better explain the goal.
Step 1. New row added to MEMBER table (by a separate bot)
Step 2. "add related distances" bot triggered by ADD action to MEMBER table - bot cycles through creating rows for each waitinglistID/groupID and calculated distance, using the LatLong data from the MEMBER and GROUP tables.

The full GROUP table runs to 26 rows.
The full MEMBER table run to ±350 rows, but after the initial set up just 3 or 4 rows a day might be added.
memberAddCount is part of the automation I have tried from this post .
I have "Trigger other bots" set.

MEMBER table

waitingListID <key>	memberID	memberFirstName	memberLatLong	memberAddCount	Related DISTANCEs
228283a9	2673171	Harley	50.833731, -1.071937	3	<list>
7c75b117	2735787	Aiden	50.829442, -1.067464	3	<list>
cf0332ba	2789742	Joel	50.832267, -1.074436	3	<list>

GROUP table

groupID <key>	groupName	groupLatLong
7890ed95	104th Portsmouth	50.82838,-1.070168
e3c99d86	1st Portsmouth Sea Scouts	50.79198,-1.109098
880dad9d	21st Portsmouth	50.801932,-1.076515

DISTANCE (is_part_of MEMBER)

distanceID <key>	waitingListID	groupID	distance
aacdf65d	228283a9	7890ed95	1
b013b53d	228283a9	e3c99d86	0.9
c11d0782	228283a9	880dad9d	2.4
1897e69d	7c75b117	7890ed95	3.2
d50382bd	7c75b117	e3c99d86	8.4
2ebdc94f	7c75b117	880dad9d	10
9fbf8f0c	cf0332ba	7890ed95	41.5
0de76f3b	cf0332ba	e3c99d86	3.2
46e54caa	cf0332ba	880dad9d	5.4

Suvrutt_Gurjar

You are welcome. There could be a few options to explore.

In the following implementation, I have added three columns in the Groups table, each one of which references the Members table.

As an example [member_1] column has the following settings. [member_2] and [Member_3] have identical settings.

Then there are three additional virtual columns in the Groups table

[Member_1_Distance] ,[Member_2_Distance] and [member_3_Distance]

Each of the columns have similar expressions for example [member_3_Distance] has an expression of

[member_2_Distance] and [Member_1_Distance] have similar expressions.

Then once the user selects three different members in the 3 member reference columns in the Groups table, their respective distances for that particular group are computed in the three virtual columns of distance.

In this arrangement, the groups table will continue to be of 26 rows and at a time distance of 3 members can be calculated.

You could implement some variations of this approach depending on your need. In multi user environment this implementation will become more complex as you may need to bring in current logged in user element in the picture.

View solution in original post

Recommendations for automation for creating multiple rows