Recommendations for automation for creating multiple rows

[gcor71]

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

[TeeSee1]

If I understand you correctly, you basically want to create 26 entries - the number of groups - to the distances table each time you add a member. (the new member - each of 26 groups pairs)

If so, I find running a bot calling AppSheet API ("Add") on a member add event the easiest.

{
   "Action": "Add",
   "Properties": {
      "Locale": "ja-JP",
      "Location": "47.623098, -122.330184",
      "Timezone": "Pacific Standard Time",
      "RunAsUserEmail": "YourEmail@google.com"
   },
   "Rows": [
   <<Start:groups[group.id]>>
      {
         "ref.member": "<<[_THISROW].[member.id]>>",
         "ref.group": "<<[group.id]>>"
      }
   <<End>>
   ]
}

[Suvrutt_Gurjar]

In general , could you update if  you wish to see the distances of all the 26 groups for all the members simultaneously ?

That will make the Distance table to have 26*350 records that is approximately 8800 records long and keep growing at the rate of 26 records for each added member?

Is the above understanding correct per current requirement?

Edit: As I was typing the response, @TeeSee1  has already given the solution for the above requirement.

Another option could be the Distance table has just 26 rows for 1 member or 52 rows for 2 members  or 78 rows for 3 members and each member as well as admins could dynamically select say 1 to 3 members simultaneously to see the distances for each those 3 member.

However the disadvantage will be that at a time only three members'  group distances could be seen. This may be undesirable in certain requirements when group distances of several members need to be seen simultaneously. The concept could be extended to say 5 or so members so that the Distance table remains relatively small.

[gcor71]

Thank you both.
I will try @TeeSee1 's suggestion as it's something new to me and I'd like to explore it.

@Suvrutt_Gurjar , you are correct in your understanding and the shear number of records was giving me pause for thought (it would also be very slow to generate the first time).  I like your suggestion of 3 group distances only per member as it's most likely these that will be needed most of the time.  The ability to Analyse the distance table will be important, so this is a helpful compromise.  I will also explore this.

Lastly, I wonder if - going for only 3 distances - I couldn't add those as virtual columns in the member record?  It would certainly work, and I could imagine selecting 1st nearest, 2nd nearest etc .  Which approach, lookup distances table or VC would impact performance the least OR is otherwise "better" from a structural perspective?

Again, thank you both.

[Suvrutt_Gurjar · Accepted solution]

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.

[gcor71]

That's so thoughtful of you to spend the time thinking through and documenting you approach!  Thank you.

I'm going to spend some time exploring the different options and see what I REALLY need (vs that I think I need).

Very much appreciate your expertise.  

[TeeSee1]

From a member perspective, you can pick the closest group by

INDEX(
 ORDERBY(groups[group.id],DISTANCE([group.latlon],[_THISROW].[member.latlong])),
 1
)

Second, third, ... closest in the same fashion.

---

@gcor71 wrote:

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.

---

If I read this correctly, you want to find the nearest group given a member and not the nearest member given a group?

Anyway, you now have two perspectives that you can work with.