Looping : 2 Loops in a single form to be executed

AppSheet AppSheet Q&A
, , ,
1

Hello Looping Experts

Request for your guidance:

I have a table by the name of service availed. In which I have 2 fields:

Pet ID: Which is an Enumlist Datatype with a base type as Ref, has a formula in its valid IF section as:

Select(Pet Details[Pet ID],AND([Customer ID]=[_THISROW].[Booking ID].[Customer ID],[Pet Status]=“Active”))

Service Name: Which is an Enumlist Datatype with a base type as Ref, has a formula in its valid IF section as:

SELECT(
Service Details[Service ID],
OR(
AND(
[Pet Coat] = ANY(SELECT(Pet Details[Pet Coat],LIST([Pet ID])=[_THISROW].[Pet ID])),
[Pet Type] = ANY(SELECT(Pet Details[Pet Type],LIST([Pet ID])=[_THISROW].[Pet ID])),
[Category] = [_THISROW].[Category]
),
AND(
[Pet Size] = ANY(SELECT(Pet Details[Pet Size],LIST([Pet ID])=[_THISROW].[Pet ID])),
[Pet Type] = ANY(SELECT(Pet Details[Pet Type],List([Pet ID])=[_THISROW].[Pet ID])),
[Category] = [_THISROW].[Category],
[Service Status] = “Active”
)
)
)

I had created a loop, where If I select multiple values in my Service Name Enumlist, then for all the selection a separate row is created under a booking. But Pet ID was Enum at that time so I will have to do 1 pet at a time.

Objective: My goal is to achieve to be able to select multiple pets & then multiple services & then the system should create all the services for each of the pet. For example, if 2 pets are selected, & 3 services under a booking, then 3 services for one pet & 3 services for the other pet should be created.

2

Sharing the updated Service Name formula:

SELECT(
Service Details[Service ID],
OR(
AND(
[Pet Coat] = ANY(SELECT(Pet Details[Pet Coat],IN([Pet ID],[_THISROW].[Pet ID]))),
[Pet Type] = ANY(SELECT(Pet Details[Pet Type],IN([Pet ID],[_THISROW].[Pet ID]))),
[Category] = [_THISROW].[Category]
),
AND(
[Pet Size] = ANY(SELECT(Pet Details[Pet Size],IN([Pet ID],[_THISROW].[Pet ID]))),
[Pet Type] = ANY(SELECT(Pet Details[Pet Type],IN([Pet ID],[_THISROW].[Pet ID]))),
[Category] = [_THISROW].[Category],
[Service Status] = “Active”
)
)
)

3

@Marc_Dillon I think you have solved a similar problem before through nested loops. Please assist

4

I tried the above nested action loop & it works for me.(https://www.googlecloudcommunity.com/gc/Tips-Tricks/Nested-Action-Loops/m-p/377956#M5211) The only problem is it works for me only when the Service ID for both the pets is same.

Now the service ID may differ if one of the pet is a labrador & one is beagle, because a service charge for beagle & for a Lab is different hence their service ID’s are different.

In my service name valid if formula is as below:

SELECT(
Service Details[Service ID],
OR(
AND(
[Pet Coat] = ANY(SELECT(Pet Details[Pet Coat],IN([Pet ID],[_THISROW].[Pet ID]))),
[Pet Type] = ANY(SELECT(Pet Details[Pet Type],IN([Pet ID],[_THISROW].[Pet ID]))),
[Category] = [_THISROW].[Category]
),
AND(
[Pet Size] = ANY(SELECT(Pet Details[Pet Size],IN([Pet ID],[_THISROW].[Pet ID]))),
[Pet Type] = ANY(SELECT(Pet Details[Pet Type],IN([Pet ID],[_THISROW].[Pet ID]))),
[Category] = [_THISROW].[Category],
[Service Status] = “Active”
)
)
)

Because of which, if the service ID’s are different then the loop is not running completely.

@Steve @Marc_Dillon @Suvrutt_Gurjar @AleksiAlkio @Aurelien @WillowMobileSys and other fellow app creators please assist..

5

I tweaked the formula to the below way because even after selection of 2 pets which are different breeds, the service name option which I was getting was for only one kind of breed. After I made the edit, I started getting 2 options in my Service name field.

But when I put this formula in the “Valid If” space, it tells me invalid service name. And if I put it in suggested values then upon saving the form, the system is hanging,

SELECT(
Service Details[Service ID],
OR(
AND(
IN([Pet Coat] ,SELECT(Pet Details[Pet Coat],IN([Pet ID],[_THISROW].[Pet ID]))),
IN([Pet Type] ,SELECT(Pet Details[Pet Type],IN([Pet ID],[_THISROW].[Pet ID]))),
[Category] = [_THISROW].[Category],[Service Status]=“Active”
),
AND(
IN([Pet Size], SELECT(Pet Details[Pet Size],IN([Pet ID],[_THISROW].[Pet ID]))),
IN([Pet Type], SELECT(Pet Details[Pet Type],IN([Pet ID],[_THISROW].[Pet ID]))),
[Category] = [_THISROW].[Category],
[Service Status] = “Active”
)
)
)

Need some proper guidance in this..

6

I am confused. Do You need help with building looping actions to add the correct Pet+Service rows? Or do you need help with inserting Valid_If expressions to for the Selection lists?

1 Like
7

The easiest way is to build this with AppSheet API & webhook.

The json would be something like..

{
“Action”: “Add”,
“Properties”: {
“Locale”: “en-US”,
“Location”: “47.623098, -122.330184”,
“Timezone”: “Pacific Standard Time”
},
“Rows”: [
<START:SELECT(Pets[ID],IN([ID],[_THISROW].[Pets]))>
<START:SELECT(Services[ID],IN([ID],[_THISROW].[Services]))>
{
“ID”: “<<UNIQUEID()>>”,
“Pet”: “<<[_THISROW-1].[ID]>>”,
“Service”: “<<[ID]>>”
},
<>
<>
]
}

8

I can understand the confusion because as & when I was trying to debug the issue myself, I had got few of my doubts clarified. I will try to explain the issue:

I hope you understand the tables which are there in the app. I have followed the same method which was suggested by Marc.

What I am assuming the issue is, that the Service Name field takes data from Service details table, in which there is an overnight boarding service of 4 types with unique service ID since the charge of the service varies basis the size of pet.

In my service availed form, when I am choosing 2 pets which are of different size, hence they further have different service ID’s the app just hangs.

I was guessing before that this is due to select statement. But the loop works fine when it comes to 2 pets with same breed hence having the same service ID.

9

Ok, it seems it is the looping you need help with. I watched the video in your other post. I will post there.

1 Like