Jump to content
  • 0

Table Level Formula Field - Creating a string from numbers


kpcollier
 Share

Question

I have a table that is used for recording units and their sizes. There are 3 possible units (IG1 - IG3) that can be submitted, and each one has a Width and a Height measurement field (IG1W, IG1H, etc.). These Width and Height fields are number types.

I am trying to create a formula on the table level that will list out these measurement fields together like IG1W x IG1H, IG2W x IG2H, IG3W x IG3H, so that the result would show something like: "23 x 33, 41 x 48, 32 x 55". However, not every record is going to have all 3 units filled out. Some may only have 1 or 2, leaving the others blank. This has made it way more difficult for me. 

IsNull(Cast([@field:IG1_W] as VARCHAR), '') + ' x ' + IsNull(Cast([@field:IG1_H] as VARCHAR), '') + ', ' + IsNull(Cast([@field:IG2_W] as VARCHAR), '') + ' x ' + IsNull(Cast([@field:IG2_H] as VARCHAR), '') + ', ' + IsNull(Cast([@field:IG3_W] as VARCHAR), '') + ' x ' + IsNull(Cast([@field:IG3_H] as VARCHAR)

The above formula is working, grabbing all of the values and putting them together in the right format. However, the blank values make it hard to control the "x" and "," for when all units aren't filled out. (ex. it would show as "10 x 12, x, x" if IG2 and IG3 were blank).

If anyone has done something similar to this, I'd appreciate some help. I am not totally set on using a Formula field either, I am open to other ideas. I just need a field in my table that lists out all the sizes together. 

Link to comment
Share on other sites

1 answer to this question

Recommended Posts

  • 0

Solution:

Replace(IsNull(Cast([@field:IG1_W] as VARCHAR), '') + ' x ' + IsNull(Cast([@field:IG1_H] as VARCHAR), '') + ', ' + IsNull(Cast([@field:IG2_W] as VARCHAR), '') + ' x ' + IsNull(Cast([@field:IG2_H] as VARCHAR), '') + ', ' + IsNull(Cast([@field:IG3_W] as VARCHAR), '') + ' x ' + IsNull(Cast([@field:IG3_H] as VARCHAR), ''), ''), ',  x ', '')

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...