Sequential numbering based on the "uniqueness" of two columns in the row

Hi

I am struggling to create an expression for the intial value for the Set No columns that is based on the “uniqueness” of the relevant User and Exercise value for that row.

I initially used the below expression for intial value of the Set No column and then realised that I needed to factor in the User and Exercise columns otherwise the Set No of the 1st Squat row would be 3 and not 1 as shown in my desilike in the below table.

(MAX(Workout Plan Exercises[Order]) + 1)

Any help on how I go about doing this?

image

???

Appologies.

This is the expression I am refereing to.

(MAX(Workout Plan Exercises[Order]) + 1)

1 Like

Maybe this?

(
  MAX(
    SELECT(
      Workout Plan Exercises[Order],
      AND(
        ([User] = [_THISROW].[Exercise]),
        ([Exercise] = [_THISROW].[Exercise])
      )
    )
  )
  + 1
)
3 Likes

Hi Thanks for the support.

I think we are close but it did not work, and I think I know why, but not sure how to adjust the expression.

So you will see that Barbell Incised Bench Press returned a 1 instead of a 2.

Take a look at the form after selecting add below.

So I think the intial value must only run after the Work Plan is selected…and that would be the issue.

I suppose if I embed your expression in an if expression that runs when Workout Plan is not blank…would I be correct?

Let me see I can try and get it to work

With Regards to the Serial Numbers, If You Must Thread.

Only the user in question is able to edit create and edit his own “Workout” …so I should be ok here right?

1 Like

I realise now that IF will not work.

I not sure how to force the intial value expression below to run only after the Workout Plan and Exercise Plan.

(
  MAX(
    SELECT(
      Workout Plan Exercises[Order],
      AND(
        ([User] = [_THISROW].[Exercise Plan]),
        ([Exercise Plan] = [_THISROW].[Exercise Plan])
      )
    )
  )
  + 1
)

Any ideas here on how to get the intial value expression to run after the Workout Plan and Exercise Plan values are added?

Am I correct that I can only achieve this with a workflow?

I see however that Workflows will be discontinued as of 10 May.

This?

(
  MAX(
    SELECT(
      Workout Plan Exercises[Order],
      ([User] = [_THISROW].[Exercise])
    )
  )
  + 1
)
1 Like

Hi Steve

I think your other formula was on the right track. I think the issue here is that:

  1. The [User] intial value is captured at the same time as the [Order] intial value of which the expression you help me on is based on. Not sure what is the sequence of how expressions are calculated. So will the initial value of the [Order] run before that of [User] as appsheet is setup to run expression from further left column to right?
  2. Either way the Workout Plan and Exercise Plan values of which you intial expression is based on is yet to be populate and by that time the intial value expression of [Order] is already calculated by then. Also keeping mind that the expression of the [Order] column has to consider values from all 3x columns to give me the correct number. i.e. [Workout Plan], [Exercise Plan], [User]

It would be great if there was a way to only run the [Order] initial value expression only after the [Exercise Plan] population.

So I have added you latest expression into the [Order] initial value.
Take a look at what happens
Forgot to save the app after setting the expression. The below give is more accurate

Is this the problem?

image

1 Like

Hi Marc

I didn’t even notice the typo.

I thought however that the expession needed to as below, as I am factoring in all columns that have an influence on the sequential numbering of the [Order] column
i.e. [User] and [Workout Plan] and [Exercise Plan]

However even if the expression below is the correct one…
I think the issue is that the [Order] initial value expression runs upon creating a row and not after populating the [Workout Plan] and [Exercise Plan] of which the values captured in these columns are needed together with the captured user in the [User] column to come to correct answer.
I am also not sure if the the Useremail() intial value expression on the [User] field is run before or after the [Order] Initial value expression?
Not sure if appsheet has some kind of logic on what should be the “flow” of how expression are run

Or am I missing something here

Here is how the app responds to the below expression

(
  MAX(
    SELECT(
      Workout Plan Exercises[Order],
      AND(
        ([User] = [_THISROW].[User]),
        ([Workout Plan] = [_THISROW].[Workout Plan]),
        ([Exercise Plan] = [_THISROW].[Exercise Plan])

      )
    )
  )
  +1
)

And how it response with the corrected expression from @Steve

(
  MAX(
    SELECT(
      Workout Plan Exercises[Order],
      AND(
        ([User] = [_THISROW].[User]),
        ([Exercise Plan] = [_THISROW].[Exercise Plan])

      )
    )
  )
  +1
)

Initial Value will continuously re-calculate during the entire Form session, until either the Form is saved, or the User manually enters a value.

1 Like

OK that interesting.

Didnt seem to pick that up. Will look again.

What’s your thoughts on the GIF showing how the app reacts with both expressions.

Any idea on what I am missing or doing incorrectly?

I have been trying to figure out what is the correct expression to use here over the weekend and have not come right. Not sure what I am missing.

Let me know if you guys have any ideas here. Would appreciate the support.

I don’t think the expression is the issue. You must be doing something weird somewhere else, and/or severely overcomplicating this. Maybe try to start over a bit? Here is my equivalent setup which works as expected:

image

image

Here is the Form for a new entry, upon selecting just user:

Then after selecting the workout:

Then even un-selecting the workout:

1 Like

Hi @Marc_Dillon

I adjust the expression to below and it is working now!

MAX(SELECT(
      Workout Plan Exercises[Order],
      AND(
        [User] = [_THISROW].[User],
        [Workout Plan] = [_THISROW].[Workout Plan]
      )
    ))
  +1

Thank for the support.

2 Likes

@Marc_Dillon

I wondering if you can help me on another expression I struggling with.

So I have the following expression used for a virtual column that I set as a key column.
This is to ensure a user does not accidentally add the same exercise to the same workout.

image


So a user will get the following error message that will inhibite duplicates as shown in the below example where the user selects “Bench Pushups” again under the “2.Chest” Workout Plan


I am however wanting to take this a step further.

How do I exclude “Bench Pushups” from the below drop down… in the 1st place… so that the user of the Workout Plan does not have to wait until saving the form to know he has a duplicate exercise. It also means the user will have a shorter drop down list for selection of only exercises that the user has not already added for that particular Workout Plan ?

So currently I have the following expression for the Valid If of the [Exercise Plan] column that orders the drop down list by:

  1. [Favourite Exercise] column where a user markes an exercise as a favourite
    …and then…
  2. [Exercise Name] in alphabetically.

How do I adapte the Valid If expression to also exclude exercise already added for that Workout Plan (eg: “Bench Pushups” value in the [Exercise Plan] column under the “2.Chest” value in the [Workout Plan] column

2 Likes