Can i run a workflow expression that will check for duplicates and NOT show/move that data?

Tiger1
Participant V

Working in workflow - i need an expression that will look at data - but will not send data if it is a duplicate of data.

Example:

Table A has column - [Location]

I want the expression to send data once PER [location]

0 25 268
25 REPLIES 25

Steve
Participant V

Probably, but your problem description is confusing.

Sorry.

If my table A looks like this:

[Location] [DSO]
Bmt 19
Bmt 19
Bmt 19
Baton Rouge 25
Baton Rouge 25

In this example - i would only want Bmt DSO - 19 sent ONE time

Does that make sense?

So you want only the distinct values,or distinct pairs in this case.

You need a template expression?

I want a workflow rule - so it can fire off a โ€œMove data to another table with a new rowโ€ - using this data.

So the new row added to my other table will be:

[Location] [DSO]
Bmt 19

But only once. Not the three rowsโ€ฆ

One workflow rule is not aware of the activity of other workflow rules, except as reflected by the data in your tables. So, for instance, if you update three rows in short order, thereโ€™s no inherent way for the workflows to know that there are three changes to handle and that some process should only be done once.

oh ok. So how would i get this process done?

Impossible to say without knowing the details of you process.

Ok. The Table A - i have been referencing looks like this:

[Sales Territory] - is basically - [location]*

In the table in my app - i have a VC:

This gets me DSO per [location]:

I want to move that view of values - to another table. But I only want one row PER [location]

***In the above view - there IS only one row showing - but if you click on it - it will show all the rows showing same data per [location]

Do you currently have a table that has one row per branch? Maybe a Branches table?

Yes. And I currently have a button - that can send a row to it. But i have to do this manually and I want it done automatically at a certain time of day:

3X_e_5_e57a4cbcaecbcde35662f0c76ca1ca51fb7e5d80.png

But how do i make it ONLY send one row? Meaning - send only one row per location?

Do you currently have a table that has one row per branch? Maybe a Branches table?

Oh, sorry. Yes:

3X_7_4_7466e481251f011a99d05fba275ddbbd220d7897.png

Hmm. Well, below are the actions you need to generate the report rows, but thereโ€™s no way to perform an arbitrary action from a report unless the report is run against each row of the table. So to schedule a report to run only one a day that generates data rows, youโ€™ll meed a table or slice with exactly one row and run the report against that row.

Action 1

  • For a record of this table: (branches table)
  • Do this: Data: add a row to another table using values from this row
  • Table to add to: (report table)
  • Set these columns:
    • (location column): [Key]
    • (DSO column):
      SUM(
        SELECT(
          table A[DSO],
          AND(
            ISNOTBLANK([Sales Territory]),
            ([Sales Territory] = [_THISROW].[Key]),
            ...
          )
        )
      )
      
  • Only if this condition is true:
    ISNOTBLANK(
      FILTER(
        "table A",
        AND(
          ISNOTBLANK([Sales Territory]),
          ([Sales Territory] = [_THISROW].[Key]),
          ...
        )
      )
    )
    

Action 2

  • For a record of this table: (report table)
  • Do this: Data: execute an action on a set of rows
  • Referenced Table: (branches table)
  • Referenced Rows: branches-table[Key]
  • Referenced Action: (action 1)
  • Only if this condition is true:

Action 3

  • For a record of this table: (report table)
  • Do this: Data: add a row to another table using values from this row
  • Table to add to: (report table)
  • Set these columns:
    • (date column): TODAY()
    • (location column): "Total"
    • (DSO column):
      SUM(
        SELECT(
          report-table[dso-column],
          AND(
            ([date-column] = TODAY()),
            NOT("Total" = [dso-column])
          )
        )
      )
      

Action 4

  • For a record of this table: (report table)
  • Do this: Grouped: execute a sequence of actions
  • Actions:
    • (action 2)
    • (action 3)

Thanks Steve,
I am running into this error?

Whoops! I should have explained! These dots:

3X_8_2_825a9a154d846eb4598cb0c386eae48134fb9945.png

represent whatever other conditions you need to add to select the appropriate rows for the sum. For instance, to heck the date. Replace the dots with any other conditions as appropriate.

OH. I gotcha. Sorry iโ€™m stupidโ€ฆ

I did something wrong. I think i didnโ€™t use a slice? I got like 1000 new rows It should have been like 11

What do the new rows look like? Screenshot?

I deleted them - they kept comingโ€ฆ
Oh i did notice also - the DSO was wrong for each - except โ€œTotalโ€

Time to troubleshoot. Make sure each actions is NOT set to Do not display. Then go to the branches table and invoke action 1 for a few branches. Do they produce the report rows youโ€™d expect for each branch?

Ok. but i think i see a problem. Branches Table - โ€œLocation Tableโ€ - i use as a ref only. It is read only. I never add data to it nor delete anything in it.

So this table should never see added/deleted/updated data:
3X_9_c_9c688dccc9983e97f0819c7a30b0d9f20df3f752.png

Thatโ€™s fine. The actions only get information, not try to add/change anything to/in it.

Weird - it just sits thereโ€ฆ:

Ok. So i forced it to sync:

That number is wrong. It should be identical the that one above it - i ran that this morning

I wanted to add. I already do math inside the [DSO] VC:

Top Labels in this Space