Need assistance with showing and not showing action buttons based on conditions

[Joe_Seiler]

Hello,

If someone could please help, that would be great!

I have 3 actions buttons.  The first button (En Route Date Time) creates a new row in another sheet, and enters in some basic info, including the date and time based on NOW()

The 2nd button stamps the On Site Date Time on that row

The 3rd button stamps the completed or Cleared Date Time on that row

What I need to accomplish is prior to a new row being created, only the En Route Date Time button is present.  Once the action is completed (a date and time is entered in the En Route Date Time column), I need that button do disappear, and only display the On Site Date Time Button.  Once that action is completed, only display the Cleared Date Time button.  Once that action is completed, start over with only the En Route Date Time button showing.

Utilizing the "Only if this condition is true" option for each action, I'm trying this approach.  I can get everything to work except the first En Route Date Time button.  It's always present, no matter what.

En Route Date Time action condition currently:

ISBLANK(SELECT(Time[Time Record #], AND([Entered By Email] = USEREMAIL(), ISBLANK([En Route Date Time]), [Jobs Record #] = [_THISROW])))

On Site Date Time action condition currently:

ISNOTBLANK(SELECT(Time[Time Record #], AND([Entered By Email] = USEREMAIL(), ISBLANK([On Site Date Time]), [Jobs Record #] = [_THISROW])))

Cleared Date Time action condition currently:

ISNOTBLANK(SELECT(Time[Time Record #], AND([Entered By Email] = USEREMAIL(), ISBLANK([Cleared Date Time]), [Jobs Record #] = [_THISROW])))

Thanks in advance for any assistance!

[Suvrutt_Gurjar]

---

Based on understanding so far, please try a action condition as follows for the first action button "En Route Date Time"

ISNOTBLANK(INDEX([Related Times][Cleared Date Time], COUNT([Related Times][Cleared Date Time])))

The above assumes that "Jobs Record" or "Jobs" is a parent table and Time is a child table of Jobs or Jobs record table.

Edit: Made some changes to the expression. Please try below expression

OR(

       ISBLANK([Related Times]),

        ISNOTBLANK(INDEX(

                                       SELECT( [RelatedTimes][Cleared Date Time],

                                                        [Entered By Email]=USEREMAIL()

                                                      ),

                                         COUNT(SELECT([Related Times][Cleared Date Time],

                                                                             [Entered By Email]=USEREMAIL()

                                                                         )

                                                           )

                                            )

                                   )

                            )

[Joe_Seiler]

Thanks for your assistance!

Tried this:

OR(

ISBLANK([Jobs Record #]),

ISNOTBLANK(INDEX(

SELECT( [Jobs Record #][Cleared Date Time],

[Entered By Email]=USEREMAIL()

),

COUNT(SELECT([Jobs Record #][Cleared Date Time],

[Entered By Email]=USEREMAIL()

)

)

)

)

)

Get this:

Column 'Jobs Record #' is used in a SELECT or list dereference expression and should be a List/EnumList of Refs

[Suvrutt_Gurjar]

Is [Jobs Record #] a reverse reference column in the Jobs table?

[Joe_Seiler]

Not using any Ref columns (mainly because I don't fully grasp the idea)

The [Jobs Record #] column exists in the Time table and Jobs table.  Its the key for both tables.

When the new row is created in the Time table from the action, it's using dereferences to pull data over.

Like for instance, in "Set these columns" in the action:

Jobs Record # = [Jobs Record #]

Project # = [Jobs Record #].[Project #]

Client = [Jobs Record #].[Client]

...

[Joe_Seiler]

But am not opposed to using Ref's, if you could explain how

[Suvrutt_Gurjar]

Okay. Can one Job table record have have multiple Time table records and is vice versa possible- a Time table record have multiple Jobs table records?

[Joe_Seiler]

For a single record (row) in the Jobs table, there could be many many many correlated entries in the Time table.  But not vise versa

[Suvrutt_Gurjar]

Then any issues you foresee in making the Time table as a  child table of Jobs table?

Reference relationships make exchanging information between tables easier.

[Joe_Seiler]

Shouldn't be an issue, the Time table will only be taking rows created from the Jobs table.  No other table will interface with it.

[Suvrutt_Gurjar]

Then please try having the [Jobs Record #] column in Time table as reference column that references the Jobs table. The key column can be a different column.

Then my suggested expression could be tried. However please do so with utmost care if it is an existing, running app.

This is so because for existing records you may need to manually add these reference relationships.

[Joe_Seiler]

In the Time table, I changed [Jobs Record #] to a ref, and set the source table to Jobs.

In the action for En Route Date Time, I used the below for "Only if this condition is true"

OR(

ISBLANK([Jobs Record #]),

ISNOTBLANK(INDEX(

SELECT( [Jobs Record #][Cleared Date Time],

[Entered By Email]=USEREMAIL()

),

COUNT(SELECT([Jobs Record #][Cleared Date Time],

[Entered By Email]=USEREMAIL()

)

)

)

)

)

I get this error.  Any insight?

Column 'Jobs Record #' is used in a SELECT or list dereference expression and should be a List/EnumList of Refs

[Joe_Seiler · Accepted solution]

Nevermind, I got it working.  I assumed when you put [Related Times] it was a placeholder for the correct column.  I have now realized that it was a virtual column added when I made it a ref.

This worked perfectly!

OR(

ISBLANK([Related Times]),

ISNOTBLANK(INDEX(

SELECT( [Related Times][Cleared Date Time],

[Entered By Email]=USEREMAIL()

),

COUNT(SELECT([Related Times][Cleared Date Time],

[Entered By Email]=USEREMAIL()

)

)

)

)

)

[Suvrutt_Gurjar]

Thank you for the update. Glad to know that my suggested solution worked perfectly without a single change.

[Suvrutt_Gurjar]

---

@Joe_Seiler wrote:

Project # = [Jobs Record #].[Project #]

Client = [Jobs Record #].[Client]

---

Also could you mention how you are using dereference expressions as above if you do not prefer reference relationships?