Expression with text string "\"

[gazmoz17]

Hi,

Before I ask this specific question may be much easier way .

Have a summary Purch ledger report, I wish to export a link to the attachments folder:

C:\ProgramData\Sage\Accounts\2021\Company.000\Transaction Attachments\Purchase Transaction 137809

So can quickly access the supporting pdfs we have attached when inputting purch inv's.

I've remapped C:\ProgramData\Sage\Accounts\2021\Company.000\Transaction Attachments to z:\ drive

e.g. Z:\Purchase Transaction137809

Excel sample export.JPG

Sage background directory.JPG

Specific Question

Tried this expression in sage and doesn't like the "\" same with concat expression only works without the "\".

“Z:\Purchase Transaction” + " " + CString(AUDIT_SPLIT.TRAN_NUMBER)

Tried searching sage literature on special character and cant find anything.

Thanks
Gareth

[brucedenney]

Hi Gareth

In these circumstances the backslash has a special meaning (this is common in lots of computer languages).

It means the what follows is not text eg \0012 is an octal code for the line break character

To "type" an actual backslash you use \\ two backslashes together.

Code: Select all

“Z:\\Purchase Transaction” + " " + CString(AUDIT_SPLIT.TRAN_NUMBER)

By the way... the name of the folder is only the transaction number when there is only one split, it is otherwise a range of split transaction numbers

So "Purchase Transaction 1128 - 1138" is the folder for transaction 1128 with 11 splits.

You will have to calculate the last transaction number in a range using the number of items

Code: Select all

“Z:\\Purchase Transaction” + " " + CString(AUDIT_HEADER.TRAN_NUMBER)

and if they are different

Code: Select all

“Z:\\Purchase Transaction” + " " + CString(AUDIT_HEADER.TRAN_NUMBER) &" - "&  CString(AUDIT_HEADER.TRAN_NUMBER+AUDIT_HEADER.ITEM_COUNT)

Now you also need to do an IF which is again poorly documented.

<Test Expression>?<what to do if test true>:<what to do when test is false>

So this is the expression I came up with for a hyperlink that opens the transaction folder of the transaction in the report in either case.

Code: Select all

AUDIT_HEADER.ITEM_COUNT>1?
"C:\\SageData\\2021\\DemoData\\Transaction Attachments\\Purchase Transaction" + " " + cstring(AUDIT_HEADER.TRAN_NUMBER)+" - "+cstring(AUDIT_HEADER.TRAN_NUMBER+AUDIT_HEADER.ITEM_COUNT-1)
:"C:\\SageData\\2021\\DemoData\\Transaction Attachments\\Purchase Transaction" + " " + cstring(AUDIT_HEADER.TRAN_NUMBER)

You also need filters on it to exclude transactions that are not purchase credits or purchase invoices.

Transaction Attachments are a NEW thing is Sage 50, the developer tools don't provide any way of setting them up, which is a real shame, there is not even a flag on the transaction to tell us if there are attachments (BTW if you don't have an attachment, the formula will still calculate the path, it just will not exist and so will return an error when you click on it)

You say this is for data entry, but that doesn't make sense as the attachments can not be attached until after the entry has been made.

I attach my test report.

[gazmoz17]

Hi Bruce,

Only just read your reply (so haven't tested) but want to say straight away thanks for going the extra mile there.

Yeah saw this morning that will always return a data path even if no attachment...so still get the error.

So I'm right in thinking there's no flag/identifier/variable whatever you want to call it for attachments in report designer .

Just seems a bit half arsed what they've implemented, would be handy adding attachments to the modules as well, e.g. customer PO to there matching sales invoice we've created in sales inv module.

The idea was to amend an existing purchase order report I run every month and send to excel....use this just to check we've keyed everything correctly (monetary values, ref's, correct nominal split where cant just use default as multiple nominal s & vary too much to just setup recurring invoice). I've built some sage expressions within this control to highlight errors but usually just export to excel and throw filters on and off for my month end check.

*So idea was add an extra column to this report, where once in excel can click into the attachment as supporting docs if I need to check anything.

But the ultimate reason to start using the attachments module was to go paperless on purchase ledger side of things.

Some how automate the matching/saving of these pdf docs by PO number.

• Our PO

• Supplier Order Confirmation

• Delivery note (only doc we don't receive on email and would need to be scanned in / needs to be anyway so can see delivery notes been signed after physically checking

• Finally Purchase Invoice

All these docs currently printed matched & stapled, indiv qty lines and prices ticked then signed by who ever. So was hoping for free pdf editor to append these docs and tick off via pdf editor... with electronic signature of person checking and with an electronic time/date stamp.

So these appended docs plus any supporting emails was what was hoping to use Transaction Attachments for but its proving hard work in all areas!

Thanks

[gazmoz17]

Argh just 2 slashes . Couldn't find it in any sage literature and the online chat couldn't help me told me ring report team. I did and lady who answered couldn't help she wasn't report trained, plus they always want to Bill you mad money for telling me a small expression which is about 5% of overall report I'm working on.

[gazmoz17]

Saw yesterday as well that the splits messed it up, but didn't have a clue how to sort that!

[gazmoz17]

Hi Bruce,

Thanks a lot for that really didn't even attempt the splits.... that's ace works a treat .

One of those things I suppose always returning a transaction pathway even if there isn't one and getting an error.

Thanks again

[gazmoz17]

Hi,

Struggling (same report) trying to change a text field: 10,000 kg to a number field for a calc.

To extract 10,000 as a number to do net/10,000kg to get me a per kg price.

Keep getting error Input string was not in a correct format.

I have used remove and length functions to remove "kg" from my string to leave me 10,000.

Expression name "Kgage":
PROJECT.REFERENCE <> ""?Remove(AUDIT_SPLIT.DETAILS,Length(AUDIT_SPLIT.DETAILS)-2,2):PROJECT.REFERENCE

Kg price final expression: :
Net/StringToFloat(Kgage)

"Net": AUDIT_SPLIT.NET_AMOUNT

Project ref.report

Any help much appreciated
Cheers
Gareth

[SomeGuy]

I suspect your problem is not to do with the number extraction as such. Testing a simple expression like this one works fine:

Code: Select all

StringToFloat("10,000")

One possible cause of the problem is with your Kgage expression:
PROJECT.REFERENCE <> ""?Remove(AUDIT_SPLIT.DETAILS,Length(AUDIT_SPLIT.DETAILS)-2,2):PROJECT.REFERENCE

What this is saying is if there is no value in the PROJECT.REFERENCE field then that will be returned instead of the result of the Remove function. In effect this means anything not linked to a project (or more specifically a project transaction) will go down this path. So in those cases what is passed to StringToFloat in your Kg price final expression is an empty string, and this would cause the "Input string was not in a correct format" error.

Another possibility is that the value returned by Remove(AUDIT_SPLIT.DETAILS,Length(AUDIT_SPLIT.DETAILS)-2,2) is not always a valid number after removing the last 2 characters. For example AUDIT_SPLIT.DETAILS could contain "Opening Balance" in some transactions so the result of the expression, and the value passed to StringToFloat, would be "Opening Balan" which of course could not be parsed into a floating point number and would cause the error message you are seeing.

One thing you could try is changing your Kgage expression to something like this:

Code: Select all

EndsWith(AUDIT_SPLIT.DETAILS,"Kg") ? Remove(AUDIT_SPLIT.DETAILS,Length(AUDIT_SPLIT.DETAILS)-2,2):"0"

That way you'd only apply the remove if it is a value that ends in Kg and return a 'safe' value for all other cases.

SG

[gazmoz17]

Hi,

Sorry I'm so late replying I saw your replies late and then jumped back into it and managed to get my report working .

Many Thanks