Format aa account code (general ledger code) for a CSV report - lookup value

(0) Share

Report

Posted on by lhowlett2022

Hi All,

If someone could please assist me with this expression, it would be appreciated.

I am creating a CSV report from a sharepoint list. On the SP list there are look up columns and one of the columns is an account code in the format of 4-7006. On the sharepoint list the column is a text column.

I have gotten as far as this expression concat('4-7', formatNumber(float(split(item()?['Course_x003a__x0020_GL_x0020_Cod/Value'], '-')[sub(length(split(item()?['Course_x003a__x0020_GL_x0020_Cod/Value'], '-')), 1)]), '000'))

When I run the report it is inserting an additional number 7, that is 4-77006 instead of 4-7006. I've tinkered with the expression but I can't figure out where I'm going wrong.

If anyone is able to assist me with this, it would be appreciated, thanks Liz

Categories:

Using flows

I have the same question (0)

All responses (2)

Answers (1)

Sort by

Verified answer

Expiscornovus 33,851 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @lhowlett2022,

Afaik the zero placeholder is to add zeros to the number. It looks like you just want to collect the last three characters of the second part of your string? Can you also share some sample input, this will help testing/troubleshooting.

If that is the case use a skip function instead (in this example I am only skipping 1 item, you might need to calculate how many you need to skip, based on the length of the string).

concat('4-7', skip(split(item()?['Course_x003a__x0020_GL_x0020_Cod/Value'], '-')[sub(length(split(item()?['Course_x003a__x0020_GL_x0020_Cod/Value'], '-')), 1)], 1))

Was this reply helpful? Yes No
lhowlett2022 11 on at

Like (0)

Report
Copy link

Link copied!

Hi @Expiscornovus

Thanks so much for your time looking at my issue. 😀 You did point me in the right direction, that is from the status of I didn't know there was a skip function 😐 to a now completed power automate.

For a bit more context for those that maybe searching for a similar solution, I have a list of transactions, that I am running a recurring report on a weekly basis to email as a CSV report. The list of transactions, has many lookup columns, including the account code or general ledger code for the item. They are different account codes for different products, the format is 4-7XXX, so the code could be 4-7010, 4-7025, 4-7050.

In tne end the expression that worked for me:
concat(split(item()?['your column name/value'], '-')[0], '-', slice(item()?['your column name/value '], add(indexOf(item()?['your column name/value'], '-'), 1)))

many thanks Liz

Cheers Liz

Was this reply helpful? Yes No