Code optimization on nested if statements

AppSheet AppSheet Q&A
1

Hi All,

I’m trying to find a better way to write this expression below.

The expression below returns a list of cards that have a combined duration closed to the requested class duration.

  • Fast_Class_Cards is an Enumlist of Ref that represents exercise cards
  • Demo_Duration is the duration of the exercise card
  • Fast_Class_Duration_Requested is the class duration requested
  • Fast_Class_Cards is an Enumlist of Ref of available exercise cards

It works fine but if I want to create class of 100 exercises, I will need to have 100 if statements…

IF(TOTALSECONDS(INDEX([Fast_Class_Cards][Demo_Duration], 1))>[Fast_Class_Duration_Requested],
	TOP([Fast_Class_Cards],1)
,
	IF(TOTALSECONDS(SUM(TOP([Fast_Class_Cards][Demo_Duration], 2)))>[Fast_Class_Duration_Requested],
		TOP([Fast_Class_Cards],2)
	,
		IF(TOTALSECONDS(SUM(TOP([Fast_Class_Cards][Demo_Duration], 3)))>[Fast_Class_Duration_Requested],
			TOP([Fast_Class_Cards],3)
		,
			IF(TOTALSECONDS(SUM(TOP([Fast_Class_Cards][Demo_Duration], 4)))>[Fast_Class_Duration_Requested],
				TOP([Fast_Class_Cards],4)
			,
				IF(TOTALSECONDS(SUM(TOP([Fast_Class_Cards][Demo_Duration], 5)))>[Fast_Class_Duration_Requested],
					TOP([Fast_Class_Cards],5)
				,
					IF(TOTALSECONDS(SUM(TOP([Fast_Class_Cards][Demo_Duration], 6)))>[Fast_Class_Duration_Requested],
						TOP([Fast_Class_Cards],6)
					,
						IF(TOTALSECONDS(SUM(TOP([Fast_Class_Cards][Demo_Duration], 7)))>[Fast_Class_Duration_Requested],
							TOP([Fast_Class_Cards],7)
						,
							IF(TOTALSECONDS(SUM(TOP([Fast_Class_Cards][Demo_Duration], 8)))>[Fast_Class_Duration_Requested],
								TOP([Fast_Class_Cards],8)
							,
								IF(TOTALSECONDS(SUM(TOP([Fast_Class_Cards][Demo_Duration], 9)))>[Fast_Class_Duration_Requested],
									TOP([Fast_Class_Cards],9)
								,
									IF(TOTALSECONDS(SUM(TOP([Fast_Class_Cards][Demo_Duration], 10)))>[Fast_Class_Duration_Requested],
										TOP([Fast_Class_Cards],10)
									,
										TOP([Fast_Class_Cards],10)
									)
								)
							)
						)
					)
				)
			)
		)
	)
)

Thank you!

2

Hello @teambelair

This is a tough one! The way I might go about this would be:

  • Configure things so that users can see the total duration of all their selected classes, in such a way that it live updates as they make additions/subtractions from the list; this way they can see the total time and configure things as they see fit.

Method A (Keep it in the form)

You could switch the EnumList input type to buttons, then show the sum(duration) just below (or even add it into the description for the buttons).
But you said you could have 100 classes; if you did this then you’d be faced with a 100 button mess in the middle of your form.

You could add in some additional fields that would allow users to filter the list of classes down, helping reduce the “clutter” of all the classes as buttons - but this still isn’t a very good option for a situation like this where you have TONS of options.

Method B (Split it out into tables)

This method requires significant modification to how you’re recording data, but ultimately will produce a smoother flow.

  • The idea is to create a child table that’s essentially a “join” table - connecting the classes and users together.
  • You’ll need a parent table, which you’ve already got; you just need to change how the list of selected classes is populated.

By splitting the class selections into their own table, now you can make use of the background record creation action to automatically create these records and add them to the users list of selected classes.

I would change the interaction flow to the following:

  1. Users start a “class selection process” - entering some basic info at the start (such as how long they’re looking for, name, email, etc.)
  2. At the bottom of that initial form, I would put an Enum (with a single option: Add Classes)
    • when you only have a single option like this, if you render the input as buttons it looks like a “control” button inside the form.
  3. For the form view of this initial view, I would set it to automatically save
    • and with that enum button as the last input, this will serve to keep the interaction flow moving.
  4. I would create a composite action that runs on the save event for the initial form
    • Inside this composite action, I would have a condition navigation action that’s looking for “Add Classes” from that enum button
      • If selected, then this navigation action fires off taking the user to a list of all the classes (see number 7).
  5. I would set up a special flag column inside the Parent table, flagging newly created records with a tag like “Adding_Classes”
  6. I would create a slice to pull out these Parent records (including a match on the UserEmail() so that the record inside that slice is unique to the user).
    • With a slice like that, you can easily use a formula to pull the ID from that parent and use that when creating records in the background.
  7. I would create a special view of the classes table, one that will only be used in this special “selecting classes state” that the app currently sits in (while that slice holds a record).
    • For the “row selected” event action, I would then create a background record creation action
      • This action creates a record inside the child table, populating the ParentID from the slice, and the classID from the row selected.
    • I would also add in a check to see if the class selected is already inside the Parent’s list of classes (in order to prevent duplicates)
      • possibly even creating an additional action that’s an alert to that fact.
  8. The last piece of this puzzle is an action button that will “close” the parent
    • Obviously this action clears that temporary flag set when the record was created.
      • which removes it from the slice, thus denoting that the “special adding classes state” is over.

Doing things this way allows your users to add classes on the fly, see the duration total, and add/remove classes to get things just right.

You’re putting the power of their total time in their hands.

There’s tons of ways you could do method B’s flow, many different ways to get to the same result. And there’s plenty of stuff I left out that would help the display of information come across better - but this is already long enough! (^_^)

Ultimately, I think the way to go about this is to just give the users the power to select their classes and see the durations on the fly.

PS: Another variation of Method B would be to create a looping action system that essentially does all the background record creation for the classes selected in the Enumlist - but only continues to run while the duration is under their max. (So many avenues)

3

@MultiTech_Visions Thanks so much for this detailed answer. I should have fully described the use case:

  1. The user is entering Fast_Class_Duration_Requested (Number field) that represents the single class duration in minutes. For example, I want a class of 14min.
  2. The user then submits the form.
  3. The system randomly create a list of 100 exercises ( Fast_Class_Cards) which have each a specific Demo_Duration.
  4. The goal is to populate the field Fast_Class_Chosen_Cards with a list of chosen exercise cards with a total durations that shouldn’t go over 14min. To do so, I simply go over the list created in step 3. and sum up subset of the list until I reach 14min.

If I understood correctly, your suggestion would work if the user was selecting manually the exercises. Sorry, I should have mentioned about the random concept.

The current code works but it is not pretty lol

3X_e_9_e97591f56bf72026fd377d98e45214fadc2e1245.jpeg
3X_e_9_e97591f56bf72026fd377d98e45214fadc2e1245.jpeg961×1286 193 KB

Another variation of Method B would be to create a looping action system that essentially does all the background record creation for the classes selected in the Enumlist - but only continues to run while the duration is under their max. (So many avenues)

This is very interesting! Wonder if you have any example on how you would do that.

4

No worries, and pretty doesn’t matter really - as long as it works!