If And formula doesn't work

[Marijn_Gremann]

Hi,

I’ve been working on an If And formula. The formula itself works. That is: when creating the field and entering the formula, I get no errors. However, the records that match the conditions the formula is describing remain empty.

I’m hoping someone smart with formulas can check mine out :grinning:

In words: I want the formula to show the right amount of service costs. When a record is linked to a specific address and rent amount, it needs to show the linked amount of service costs.

My formula looks like this:

IF(
AND(
{Address} = “Value 1”,
{Rent} = “A”
),
“Service costs A”,

IF(
AND(
{Address} = “Value 1”,
{Rent} = “B”
),
“Service costs B”,

IF(
AND(
{Address} = “Value 1”,
{Rent} = “C”
),
“Service costs C”
)
)
)

I can’t figure out why it’s not giving any outcome. Any ideas on this matter? Thanks in advance!

[kuovonne]

Can you show screen shots of what you want, including all the relevant fields?

Does the formula show values for any records at all? If not, try checking the values in the fields and look for hidden characters such as spaces, or differences in upper case / lower case.

You also mention that you want the formula

Marijn_Gremann:

to show the linked amount of service costs.

Are these amounts in lookup or rollup fields in the same table or are they in a different table and you want the formula to create a link? Formulas by themselves cannot create links.

[Marijn_Gremann]

Hi, thanks for responding :grinning:

I would like the data in A to automatically generate the data in B, but only for a specific address. Now I have to manually fill in both.

The amount of service costs are merely in the formula: based on the amount of rent, the formula should generate the related amount of service costs in the formula field. So there’s not a lookup field or anything of the like.

I noticed that I can’t do anything about formatting after entering the formula, because “Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date.” This might be a clue to what’s wrong with the formula, but I’m missing it so far.

Maybe it’s because the first part of the AND isn’t numeric: it’s the address related to the records the formula must be applied to. The formula is actually saying: IF address is X and Rent = Y, Service costs = Z. I now wonder if all values should be numeric for this to work?

[kuovonne]

Marijn_Gremann:

I noticed that I can’t do anything about formatting after entering the formula, because “Your result type is not a number or a date.

Your formula produces a text string. The literal text "Service costs A” (or B or C). If you want field values, you need to use curly braces. {Service costs A}. However your screen shot does not show field names so it is hard to tell.

[Marijn_Gremann]

The formula should produce a text string: the desired outcome is a given number (€ service costs). I don’t want a field value, because I want the service costs to be generated by the formula itself.