Formula question

Discussion forum about all things Report Builder (all versions).
jpro

Formula question

Post by jpro » 28 Sep 2015, 14:31

Is there a method of creating a formula that will keep placeholders for a specific number of characters if the data field is null?

I need to create a field that is a concatenation of 3 separate fields but if one of the fields is null I need to keep the placeholder for that number of digits (9 in this case).

Scott
Australia
Posts: 414
Joined: 13 Mar 2017, 23:31
Location: Sydney, Australia

Re: Formula question

Post by Scott » 30 Sep 2015, 02:02

How about:

Code: Select all

sConcat = SalStrRightX('000000000' || SalNumberToStrX(nField1,0), 9) ||
   SalStrRightX('000000000' || SalNumberToStrX(nField2,0), 9) ||
   SalStrRightX('000000000' || SalNumberToStrX(nField3,0), 9)

Dave Rabelink
Founder/Site Admin
Founder/Site Admin
Netherlands
Posts: 3388
Joined: 24 Feb 2017, 09:12
Location: Gouda, The Netherlands

Re: Formula question

Post by Dave Rabelink » 30 Sep 2015, 06:50

Another way:

Code: Select all

Set sField = SalFmtFormatNumber( nField1 + 0, "000000000" ) || SalFmtFormatNumber( nField2 + 0, "000000000" ) || SalFmtFormatNumber( nField3 + 0, "000000000" )
This will ensure that a field is always 9 characters long (having 0..9 prefixed zero's).
The "+ 0" of the fields is to force NUMBER_Null to be converted to 0 (zero) value which becomes a field with 9 zero's.

(this is when you are sure the fields include positive numbers, zero and NUMBER_Null and a max of 9 digits)
Regards,
Dave Rabelink

Image
Articles and information on Team Developer Tips & Tricks Wiki
Download samples, documents and resources from TD Sample Vault
Videos on TDWiki YouTube Channel

jpro

Re: Formula question

Post by jpro » 30 Sep 2015, 15:05

Thank you both for your suggestions, unfortunately I should have mentioned that I am trying to do this in Report Builder 6.0 and I am receiving an error when using the sConcat & Sal commands.

What I really need to do is have a string value that appears as 9 underscores or equals signs only when it is null (because it is used in a barcode - also something I didn't mention originally - sorry).

My data is working when it has a value in all 3 parts of the concatenated field like this - '*'||IDENT||OLDITEMNO||LT_NOTES||'*' however when the OLDITEMNO is null I obviously only get the 2 fields of data but would need the 9 placeholders for it to work correctly.

Any help is appreciated.

martinenco
Argentina
Posts: 101
Joined: 10 Apr 2017, 18:45
Location: Ushuaia, Argentina

Re: Formula question

Post by martinenco » 30 Sep 2015, 23:07

I Think that this can do the trick.

I follow the Dave's logic.
Try this formula in a field of the qrp.

StrIFF( OLDITEMNO + 0 , NumberToStrPicture( OLDITEMNO, '000000000 ), '=========', NumberToStrPicture( OLDITEMNO, '000000000' ) )

I don't tested, It's only a guess.

Enrique

jpro

Re: Formula question

Post by jpro » 01 Oct 2015, 13:30

Enrique,

Looking at your suggestion, I believe this would work if the value was a number however it is actually a string.

Is there something similar that would work as you suggested for a string value here?

Thanks.

wardies
Great Britain
Posts: 86
Joined: 21 Mar 2017, 10:44
Location: UK

Re: Formula question

Post by wardies » 01 Oct 2015, 16:53

How about:

Code: Select all

'*'||IDENT|| StrIFF( StrCompare(OLDITEMNO,''), '', '_________', OLDITEMNO ) ||LT_NOTES||'*'
or:

Code: Select all

'*'||IDENT|| StrIFF( StrLength(OLDITEMNO), '', '_________', OLDITEMNO ) ||LT_NOTES||'*'

jpro

Re: Formula question

Post by jpro » 01 Oct 2015, 17:17

Wardies - Thank you Very Much!

This worked perfectly for what I was trying to do - Much Appreciated.

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests