• 0

Table Level Formula Field - Creating a string from numbers

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.

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 ', '')`

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.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.