Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

Text Mode Search and Replace

Avatar

Level 2

I need to shorten the value of a field to the Value Field instead of the Label. I'm trying to get this conditional to work but it seems to be failing. Is there a better way to do a string replace?

valueexpression=IF({project}.{DE:Deliverable Aspect Ratio} = 'HD - 16:9 - 1920 x 1080', 'HD', IF({project}.{DE:Deliverable Aspect Ratio}='Square - 1:1 - 1080 x 1080', 'SQ','null'),'null')

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

5 Replies

Avatar

Level 10

Hi - I've never put null in an IF statement. I tend to use two quotation marks with no space. (Also you have a lot of spaces in there. I've removed the unnecessary ones)

valueexpression=IF({project}.{DE:Deliverable Aspect Ratio}="HD - 16:9 - 1920 x 1080","HD",IF({project}.{DE:Deliverable Aspect Ratio}="Square - 1:1 - 1080 x 1080","SQ",""),"")

Avatar

Level 2

Thanks @Anthony Imgrund‚ . I always forget about the spaces, I typically add them in out of habit and for legibility's sake. I put those "null" strings in as placeholders. I'll need to add a bunch more conditionals to cover all the options coming into this field. I'm guessing there isn't a better way to do it that I'm approaching. That was my question.

Avatar

Level 10

Gotcha! Yeah, when you have long complicated one it can seem tedious. What has helped for me is do them as separate IF statements and then start combining them from the bottom up and replacing the TRUE or FALSE section (depending on your needs)

Start with

IF({project}.{DE:Deliverable Aspect Ratio}="HD - 16:9 - 1920 x 1080","HD",FALSE)

IF({project}.{DE:Deliverable Aspect Ratio}="Square - 1:1 - 1080 x 1080","SQ",FALSE)

IF({project}.{DE:Deliverable Aspect Ratio}="Other","Other",FALSE)

Then have

IF({project}.{DE:Deliverable Aspect Ratio}="HD - 16:9 - 1920 x 1080","HD",FALSE)

IF({project}.{DE:Deliverable Aspect Ratio}="Square - 1:1 - 1080 x 1080","SQ",IF({project}.{DE:Deliverable Aspect Ratio}="Other","Other",FALSE))

Finally

IF({project}.{DE:Deliverable Aspect Ratio}="HD - 16:9 - 1920 x 1080","HD",IF({project}.{DE:Deliverable Aspect Ratio}="Square - 1:1 - 1080 x 1080","SQ",IF({project}.{DE:Deliverable Aspect Ratio}="Other","Other",FALSE)))

Hope that helps

Avatar

Level 10

I've also found using a text editor with color helps too, as it lets you keep the closing parens straight after assembly. I keep an offline file where I store the more complicated calculations and nested checks for later reference and re-editing.

I miss Filemaker's CASE function and it's general calculation editor at times like this; both made it easier to create and see how complexed nested functions worked.

Avatar

Level 2

Still coming up blank. Could it be that this field is from a check box and the user is allowed to check more than one option? Maybe the conditional is looking for the entire result to match one of the options? But if there are more than one checked it throws it off?