issues using Automation generate pdf with multiple rows using <<Start>> <<End>> and send by email

[Ruxandra]

Hello!

I used <<Start>> <<End>> in a template to generate pdf with multiple rows in a table; it works, I have dynamic rows added in my table designed in the template.

In my automation I set to send email for "Adds" in the Db table.

But, I have two  problems:

1. I receive as many emails as there are rows generated inside <<Start>> <<End>> expression; for example; if the pdf file has in the designed table 5 rows, I receive 5 emails instead of only one;

2. first emails do not contain all the rows from template because the sync is in progress (it takes some time to write in the appsheet DB that I use for).

Could someone help me with some suggestions to solve these problems (have only one email sent and only after all data is saved in the DB?

Many thanks!

Regards!

Ruxandra

[MiguelPilo · Accepted solution]

I have some suggestions for your case:

1. The "Data change type" of your bot need to be Updates (Wait fot condition at the end).
2. Add a new row in your table of chemicals test [Select_test].
3. Action 1. Create an action to set values in [Select_test]=IF([Select_test]="Selected", " " ,"Selected") Could be display inline attached to [Select_test]
4. Second action: add a new row to a another table (you already have this)
5. Third action: Execute an action on a set of rows; For a record of & Referenced Table: "Chemicals test";
   Referenced Rows: Select( Chemical test[Key Column], [Select_test]="Selected"); Referenced Action: Add a new row to a another table "Second action"
6.  Create a Format Rule to easy visual identification of Selected rows.

I imagine that rows copied in second table include a "order number" column or something like this. So the next is formulate de condition for your bot: 

• Condition (Compare number of selected test and number of lines in this order):
  Count( Select( Chemical test[Key Column], [Select_test]="Selected") )=
  Count( Select (Table2[key Column],[Order_number]=[_Thisrow].[Order_number]) )

If you want to use the action 1 and action 3 at the same multi selecting rows, add a new grupped action.

[AleksiAlkio]

You would need to have a specific value in the last row that only triggers the Bot.

[Ruxandra]

Hello!

I use an Action to copy selected rows using a Table1  view (there are many rows selected with no particular order in the table) to another table 2; the automation is based on the table 2 for "Data change Type" =Adds. 

Could you give me more details about how could I identify that the last row is copied and which is the last row? Thank you very much for your help!

Regards!

Ruxandra 

[Marc_Dillon]

I use a method in one app for something similar that I didn't actually expect to work, but it did. I created several records via the API, then I have another bot with an event condition checking the key column against the return of a MAXROW() expression. You'd think it wouldn't work because each individual record would be the maxrow when it is individually created, but it actually does work and it only fires on the last-created record. It could be due to the records being created via the API all in some big chunk at the same time or something. So maybe not replicable if you're using an action set to generate the records, but might be worth a try.

Alternatively, you should be able to know ahead of time how many records are selected, so you could build that in to your event condition, checking if the amount of new records matches the number selected.

If you can describe your setup in a bit more detail, I could maybe determine a better method.

[Ruxandra]

Hello!

I have a Table View on a table used as a template of data (many rows and columns). Using this table View,  the user can change data in a column, then he selects all rows where he has changed data, then he click on Action button and rows are copied in Table 2. Table 2 has an automation to create a pdf file for all these copied rows and to send it as attachment to an email.

It is a template with some tests, user has 50 rows of chemicals tests, and he could use 1, 2 tests or 10 or 40 tests. This is the idea.

Thank you!

Regards!

Ruxandra 

[Ruxandra]

I tried using Maxrow(). It doesn't work, I think because rows are not copy in bulk, but one by one, so very row is an "Add".

[AleksiAlkio]

One simple option is to add another action button that the app user clicks when it's the last one.

[Marc_Dillon]

Sounds like a very similar setup as the app that I described. Are you using bulk row selection and running an action on all rows? Instead of running the copy action here, you can instead run a flagging action that sets a lookup-able value to each of the selected rows. Then trigger another action that fires off the copying and etc. This way you can know the count of selected rows. 

[MiguelPilo · Accepted solution]

I have some suggestions for your case:

1. The "Data change type" of your bot need to be Updates (Wait fot condition at the end).
2. Add a new row in your table of chemicals test [Select_test].
3. Action 1. Create an action to set values in [Select_test]=IF([Select_test]="Selected", " " ,"Selected") Could be display inline attached to [Select_test]
4. Second action: add a new row to a another table (you already have this)
5. Third action: Execute an action on a set of rows; For a record of & Referenced Table: "Chemicals test";
   Referenced Rows: Select( Chemical test[Key Column], [Select_test]="Selected"); Referenced Action: Add a new row to a another table "Second action"
6.  Create a Format Rule to easy visual identification of Selected rows.

I imagine that rows copied in second table include a "order number" column or something like this. So the next is formulate de condition for your bot: 

• Condition (Compare number of selected test and number of lines in this order):
  Count( Select( Chemical test[Key Column], [Select_test]="Selected") )=
  Count( Select (Table2[key Column],[Order_number]=[_Thisrow].[Order_number]) )

If you want to use the action 1 and action 3 at the same multi selecting rows, add a new grupped action.

[Ruxandra]

Hello! 

I tried to do something as you described above.

I used an action to add a text in another column Y, foe example "finished", then I copied rows using another action to table 2.

I wrote the condition in the bot to count (select) all id from table 1 where column Y='finished" that should be equal to count all id from table 2 where column Y (I also have the same column in the table 2) is="finished"; the expression is valid.

If I also add another condition in the second "Count" , to match the order number, for example, the automation doesn't work, there is no email sent.

I tested separately the expression, using "Test" button, and I saw that the result is No for this case.

If I keep only the condition with column Y ="finished", all the results are Yes, even if I have only 3 rows where the Column y has "finished" filled in. I don't understand why?

I tested every action step by step, without groupping, just to verify that every part works well.

Thank you for your help!

Regards!

Ruxandra

[MiguelPilo · Accepted solution]

Please share a screenshot of bot test detail.

But i think that the problem is in second part of your formula condition:

"I wrote the condition in the bot to count (select) all id from table 1 where column Y='finished" that should be equal to count all id from table 2 where column Y (I also have the same column in the table 2) is="finished"; the expression is valid."

Your table 2 always will have column Y="finished" so, this column is unnecesary

I think that your condition have to be simple as i was mencionated:

• Condition (Compare number of selected test and number of lines in this order):
  Count( Select( Chemical test[Key Column], [Select_test]="Selected") )=
  Count( Select (Table2[key Column],[Order_number]=[_Thisrow].[Order_number]) )

[Ruxandra]

Hello again!

Finally, it works, your condition is ok; I've tested using a new robot, step by step, and the problem was "data change type"; if I also select "Adds", the automation works very well, there is one single email sent and my pdf file has only the new rows.

So, it works if "Adds" and "Updates" are selected, using the condition you described (I didn't tested if it works only using Data Change Type  "Adds"). I am writing here for anyone who needs it.

Thank you!

Regards!

Ruxandra

[Ruxandra]

Hello!

I've made some changes on the condition of the bot (I used number instead text for Selected column), I also added for Table 2 another condition, and now if I test the condition, it filters ok the rows, I see exactly 4 rows, as I tested; without the new condition, having also "Selected=1" in Table 2,  I see as results "Yes" many rows, not only my 4 rows. Yes, I know I will have selected =1 , but I can use an action to reset to 0.

Count(Select(ChemicalTest[IdDeterminare], [Selectat]=1))=
Count(Select (Table2[ID],AND([NrComanda]=[_Thisrow].[NrComanda],[Selectat]='1'))) 

The problem is, even if the the condition seems to be ok, the automation didn't work, there was no email sent; I've checked in Monitor Automation, and the automation didn't run at all and there is no error there.

Thank you!

Regards!

Ruxandra

[Ruxandra]

Hello again!

I 've found another solution, a combination of suggestion on this topic, using "last row" and user "triggers" himself the bot in order to receive the pdf using some actions on Table 2. 

However, I would prefer your automatic solution. But, I don't understand why the bot doesn't run using Count ....= Count .... condition; everything seems to be ok, I tested the condition, I read the log in "Audit History", I checked the Automation Monitor (in the Automation Monitor no error appears either, but neither the line of success is not registered, so the Automation didn't run).

Thank you!

Regards!

Ruxandra