Extract customer name from filename in the folder table

[Atlas]

I have a folder table and I used to create a file name by combining the values of the INVOICE_ID, INVOICE_DATE, and CUSTOMER_NAME columns from the invoice table. The TEXT function is used to format the INVOIC_DATE as "YYYY.MM.DD". However, instead of generating the correct string, the resulting file name contains a "-" instead of a "#".

CONCATENATE([INVOICE_ID],  
                         "_",
                              TEXT([INVOICE_DATE] , "YYYY.MM.DD"),
		                "#",
			            [CUSTOMER_ID].[CUSTOMER_NAME])

FT-221212-96599F68_2022.12.12#ATLAS.pdf
FT-221212-96599F68_2022.12.12-ATLAS.pdf

Any help is appreciated in extracting the Customer name from the generated filename.

[Suvrutt_Gurjar]

You may want to make your requirement clearer. May we know if you having issue creating a file name that has a "#" in it or you are having issue in extracting the name "ATLAS" as in the shared example file name string.

[Atlas]

Hello Dear  @Suvrutt_Gurjar , this is the first time I have worked with text strings. I read help articles, searched the forum, and tried to extract the hashtags from the text strings but failed when I tried to create a file name with a hashtag before the customer name. I wonder why it was unable to create a file name with a hashtag, and I would like to know if there was another way to extract the customer name from the filename string.

Thank you for your time and precious contribution.

[Suvrutt_Gurjar]

Hi @Atlas ,

Thank you for the details. I am able to create a file  name containing "#" when the file is created through automation bot. Please see the name generated in the left hand top corner of the image highlighted with green border.

I purposely used exactly the same expression as yours except column names as per my test app. The expression I used is

<<CONCATENATE([Name], "_", TEXT(TODAY() , "YYYY.MM.DD"), "#", [State])>>

So you may want to double check why the file name omits "#" in your case.

as for extracting customer name, in general it should be possible, as you are building the file name itself with customer name. You may want to share where and how exactly you wish to extract the customer name from the file and the community could suggest. Options could be simply using the customer name column or using EXTRACT() and SUBSTITUTE() functions.

[Atlas]

I checked the expression, it doesn't work as intended. I don't know why it is changing the hashtag with "-" sign. Please check the picture below.

I want to add a virtual column in PDF_INVOICES table to use the extracted customer name here.

[Steve · Accepted solution]

File names may not include a hash character (#).

Instead of hash, use underscore (_), then use this to get the username:

INDEX(SPLIT([Filename], "_"), 3)

[Atlas]

Hi Steve, thank you for your reply.

---

@Steve wrote:

Instead of hash, use underscore (_), then use this to get the username:

INDEX(SPLIT([Filename], "_"), 3)

---

The expression above leaves the " .pdf " at the and.  But this help to make me an expression like this.

INDEX(SPLIT(INDEX(SPLIT([File], "-"), 4),"."),1) 

Thank you!