Solved

How to populate a dimension column from a formula

  • 4 January 2023
  • 2 replies
  • 183 views

Userlevel 2
Badge +2

Hello community,

 

I’m trying to populate the very last column of the below Transaction list, which is set as dimension, which is already defined. I could populate the column line by line by selecting the values from the dropdown but there are lots of lines. 

 

That’s why I’m trying to use a formula to populate the dimension via formula.

The formula should bring the text from the Entity column (set as a dimension) and concatenate with the result with an if statement aiming to provide “Account code” text column (circled) in case it’s’ populated, or in case “Account code” is blank provide the “Account name” text column (highlighted in blue)

if (Transactionlist.AccountCode = “”,Transactionlist.AccountName,Transactionlist.Accountcode)

 

The result should be: Entityname2202 or EntitynameBank

Thanks for your help.

icon

Best answer by francois 4 January 2023, 18:40

View original

2 replies

Userlevel 6
Badge +14

Hi!

It looks like you’re looking for the IFBLANK function! Pigment actually makes a difference between an empty cell and values. Your test should not be based on empty text (aka Value="") but rather on if the cell is blank.

You could also look into ISDEFINED, ISBLANK and ISNOTBLANK that could be also interesting to read on the topic.

Using IFBLANK(Transactionlist.AccountCode,Transactionlist.AccountName) will produce a text result however, that you can then combine with & to append the entity name. Since the entity property is dimension formatted, you can’t join them by putting them side-by-side - you can however chain properties to get the entity’s name, like so:

Transactionlist.Entity.Name & IFBLANK(Transactionlist.AccountCode,Transactionlist.AccountName)

 

This will also produce a text result though, which cannot be used as a formula for a dimension-format property (it looks like it is based on your screenshot).

You can either turn that property to text-format, or If you want to identify an item in a specific dimension, you could look into the ITEM function to find the relevant entity.

 

Since this is a transaction, you won’t be able to easily try things in the Formula Playground - however, if you feel lost, I actively recommend you making a test dimension with all the relevant properties, and try formulas in the Formula Playground, which will really help build each individual part of your formula, going from joining text to turning this into an item from a dimension.

 

If this is not clear enough, please provide additional details so that I can better understand how to help!

Userlevel 2
Badge +2

Thanks for your knowledge François, clear and helpful.

Reply