Key value erroneously and automatically replaced by alternative value in File Name Prefix expression

[Gonzague_Issen1]

Dear all, 

I can't seem to solve this alone and kindly request your help. To easily manage the pdf files generated by automation, I am building an expression for the File Name Prefix that includes the key of the table that the bot queries in the event definition. It is an email address and the column is called [Email] while the table is called "employee". To get the full picture, the pdf creation task of that same bot works against another table, called "expense", which also contains an [Email] column, which is a Ref type to the "employee" table. Here is the formula, plain and simple:

CONCATENATE("NDF-",TEXT(TODAY(),"yyyymmdd"),"-",[Email])

But rather than generating NDF-20230822-xxx.yyy@mycompany.com.pdf  (I ticked off the automatic timestamp), I am getting NDF-20230822-xxx yyy.pdf with xxx yyy being the first and last name associated with the email. I have toyed with some workarounds in the expression like "<<[Email]>>" and TEXT([Email]), and also with the position of the Email in the formula, but to no avail.

I initially thought it was due to the [Email] column being marked as PII, but I checked that off in both tables. I also thought that it was caused by the table label, which was indeed a column with first and last name, but I now have both the key and the label attached to the same [Email] column. I have re-generated the tables, deleted the task and created a new one, etc. Oh and I have also tested the expression with the Test feature in the expression window, and I do get the proper output! So I just can't figure out why the key value is replaced by something else.

Thanks a lot for your help!

Cheers

[AleksiAlkio]

If the "Email" column is a key and a Ref column, it reads the label value from your other table and you probably have the name column as a label there.

[Gonzague_Issen1]

Dear Aleksi, thanks for your quick answer.

That's what I thought, but as I explained, I changed the label so that [Email] is now both my key AND label column in that "employee" table. In the "expense" table, the label column is something irrelevant (it's a description). I re-generated the table too...

How could that variable be sticky somehow ?

[AleksiAlkio]

From which table the Bot is triggered?

[Gonzague_Issen1]

the "employee" table

[AleksiAlkio]

So, you have a Data change Bot and you trigger that from the Employees table with your file name formula. What is your column type in [Email] column in the Employees table? Just trying to get the whole picture from your situation.

[Gonzague_Issen1]

Sorry, it's a scheduled event bot, not a data change bot. 
The [Email] column is an email type. 

[AleksiAlkio]

Are you using "For each row in this table"?

[Gonzague_Issen1]

Yes

[AleksiAlkio]

Maybe you should investigate from the opposite direction. Do you have the first-sure name value saved to somewhere else?

[Gonzague_Issen1]

I do: in the "employee" table, I have a column called [First and last name] which was originally my label column. So it was not all surprising to see this behavior at first. But now the label is clearly different; it's the email address, same as the key. That's why it feels "sticky"... Could it be cached somewhere else ? Beyond regenerating the table, is there any other way to delete/reset all cached values ?

[AleksiAlkio]

Are you using stable versions with your app? Even you are, the scheduled Bot should use the latest version. What happens if you trigger the Bot manually?

[Gonzague_Issen1]

Thanks for not dropping the ball!
I believe I am using stable versions: I didn't tick "preview" options in the settings. 
I only ran the Bot manually so far. 

[AleksiAlkio]

Set the latest version to your stable version and then test if that has an affect.

[Gonzague_Issen1 · Accepted solution]

Dear Aleksi,

Hurray, I found my mistake. While I was using the "employee" table as the scheduled event trigger ("What table to query?"), I was using the child "expense" table to run the task of creating a new file ("What entity table does this task work against?"). By selecting "employee" as well for the latter, everything worked! 

It's a wonder how everything else worked just fine except for that expression... I don't understand why the same table is automatically selected by default.

Thank you again for your persistence!

Cheers

[AleksiAlkio]

Good that you were able to find it!