Action in a table for another table?

[Joe_Seiler]

I'm having trouble understanding how to properly set this up.  Can anyone help out?  Particularly with ref's, and actions across tables

Table 1:  Jobs

Table 2:  Time

What I need to do is have an action in Table 1 that executes an action tied to Table 2, as well as carry some fields over from Table 1 to Table 2

So, the Jobs Tables has jobs listed.  I need a "Clock In" action in the Jobs record that will execute the Action "Clock In - Time" in the Time table.

i also need to have various columns from the Jobs table duplicate over to the Time table

The Time table will be an ongoing list of all clocked time entries from all Jobs records

Anyone know where I need to start?  I believe first I need to get the ref's figured out first, and then the double action.

Thanks in advace!

[Markus_Malessa]

You would add an action 'For a record of this table' would be Jobs Table, 'Do This' would be the same as my screenshot, 'Table to add to' would be Time Table. Then in the 'Set these columns' add each column that you would like to have set. If there is a relationship column it would be YourRelationColumn = [keycolumnofJobsTable] (can be [_THISROW]). 

[Joe_Seiler]

Thanks for your help!

I think I'm almost there. 

In the action "En Route"

For a record of this table:  Jobs

Do this:  Data: add a new row to another table using values from this row

Set these columns to:  I used dependents [Key].[Column I need]

So that worked perfect!

Now, here is the tricky part I think.  I need to add another action to the table Jobs, but only update a one column in Time.

Basically, the above was great for the original Time entry, but now I need to edit the record in Time, but with an action in the Jobs table.  I need to add "On site", and cause an update to that column with NOW()

Any ideas?

[Markus_Malessa]

Not sure if I am following correctly. I thought the action from Jobs to enter an entry in Time was supposed to do both. So under set these columns you would just add your 'Timestamp' column and the expression would be NOW().

[Markus_Malessa]

Might be helpful to know what all columns need to be set initially and then what columns need to be updated after. Seems like what you might be after is a clock in/clock out functionality? Is your question how do I identify the most current clock in row and then have an action to clock out on that same line with a separate action from the Jobs table?

[Joe_Seiler]

Seems like what you might be after is a clock in/clock out functionality? Is your question how do I identify the most current clock in row and then have an action to clock out on that same line with a separate action from the Jobs table?

That is a much better way to work it for sure.

Basically, the action in jobs created a row in Time, and sets the clock in time to NOW().  Then a second action in Jobs that finds the most current row in Time and updates the clock out column with NOW()

[Markus_Malessa]

This requires two actions. I would start with the action for the Time table.

So 'For a record of this table' choose Time table, 'Do this' would be 'set the values of some columns in this row', 'Set these columns' choose [ClockOut] and for the Expression NOW().

Then add another action under the Jobs table:

'For a record of this table' choose Jobs table, 'Do this' choose execute an action on a set of rows, 'Reference table' select the Time Table, 'Referenced rows' probably the following expression: SELECT(Time[KeyColumn], AND([JobRefColumn] = [_THISROW], ISBLANK([ClockOut])) This would however choose all rows for the particular job where there is no clockout time yet, 'Referenced action' would be the name of the action that you set up in Time table to update the clock out time. You may also want to consider setting a condition in the behavior or adding filters into the SELECT() function to better narrow down which row you need to clock out on.

[Joe_Seiler]

Yeah, so I think I do need to narrow down with the SELECT function.  I may have multiple techs clocked in on the same job at the same time, so ISBLANK wont be quite enough. I think USEREMAIL() would be the right filter, which is a column in the Jobs table, as well as the Time table.  No idea how to write that though

[Joe_Seiler]

Column name for useremail in the Jobs table is:

[Tech #1 Email, [Tech #2 Email]...[Tech #10 Email]

Column name in the Time table is:

[Entered By Email]

[Joe_Seiler]

As well as a column in both the Jobs and Time tables [Job #] to correlate

[Joe_Seiler]

So this works:

SELECT(Time[Record #], AND([Fusion Job #] = [_THISROW], ISBLANK([On Site Date Time])))

Except I need to add an additional restraint based on the USEREMAIL() of the Time table, because I may have multiple different tech's clocked in on the same job at the same time, all with a current ISBLANK clock out time.  The Time table useremail column is [Entered By Email], which is an additional constraint.

So would it look something like this?

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

That doesn't work...

Any advice on this is most appreciated!

[Markus_Malessa · Accepted solution]

I would possibly suggest going a slightly different route using MAXROW() like this:

LIST(MAXROW("Time", "On Site Date Time", AND([Entered By Email] = USEREMAIL(), ISBLANK([Off Site Date Time]), [Fushion Job #] = [_THISROW]))

//OR//

SELECT(Time[Record #], AND([Entered By Email] = USEREMAIL(), ISBLANK([Off Site Date Time]), [Fushion Job #] = [_THISROW]))

The first formula will only grab a single row matching the largest 'Clock in Time' where the clocked in user is the current user and the clock out time is blank and the Job # is the currently selected job. The LIST() function is necessary here to convert a single record to a list since your action is running on a list of rows.

The second formula would grab all rows where the clocked in user is the current user and the clock out time is blank and the Job # is the currently selected job.

I would also make sure that you set limitations on the behavior for when the action is available.

ISNOTBLANK(MAXROW("Time", "On Site Date Time", AND([Entered By Email] = USEREMAIL(), ISBLANK([Off Site Date Time]), [Fushion Job #] = [_THISROW]))

//OR//

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

[Joe_Seiler]

I really really appreciate all your help.  Both solutions worked flawlessly!

Thanks again!

[Markus_Malessa]

Glad it worked for you. Just remember to set a limitation in your behavior section on your 'clock in' action also, so that there can't be more than one row with a clock in and an open clock out. I presume something like this should suffice:

ISBLANK(SELECT(Time[Record #], AND([Entered By Email] = USEREMAIL(), ISBLANK([Off Site Date Time]), [Fushion Job #] = [_THISROW])))