Tuesday, January 6, 2009

QUOTEWERKS:tip on using layout formulae to trim strings (a real world example)

The below formula uses a function to get a set of numbers from an 18 digit field.

So in this case documentheaders->soldtoponumber the format of the field is 0000.0000.00.00.00

What one of our clients wanted was that to filter out and generate the report based on the middle 4 digits highlight above.

What we did was to use a dialog prompt at the start of the report so that the report would generate based on the 4 digit numbers. So how to get around this was to include a filter within the report that related to the dialog prompt. Below is the formula used within the report.

Dlg->siteref=last(first(documentheaders->soldtoponumber,10),4)

So to break the formula down, the Dlg relates to the dialog prompt at the start of the report, the siteref is the name of the dialog prompt and the rest is the formula.

To break the rest of the formula down, you always start within the brackets first –

Last(first(documentheaders->soldtoponumber,9),4)

First 9
0000.0000.00.00.00

Last(first(documentheaders->soldtoponumber,9),4)

Last 4
0000.0000.00.00.00

So now you can see how the formula is working to generate the report based on the 4 digit number entered in the dialog prompt.

For further assistance with QuoteWerks document and report layout authoring, please do not hesitate to get in touch with us on 0844 357 7360 or 01782 564252 or email sales@hilltopsit.co.uk as well as having a host of report layout examples that we have developed over the years, we offer very competitive hourly rates for building layouts and reports specific to your business's requirements.
We look forward to hearing from you!

No comments: