Skip to Content
0
Sep 30, 2020 at 07:14 AM

Formula Field issue

27 Views

I have a report that is experiencing an issue with two formula fields.

I am pulling data from a table and there are two records pulled for each key ID

We have a table for our Inspections, the table has multiple records for an inspection with a question and and answer. The answer field is a 250 nvarchar field. In this case there are 7 different "Inspection Templates" being reported and I need to have the answer converted to a numeric value.

I have defined two separate formula fields as follows

Field #1 ==> FUEL_100

if ({INSPQUESTION.QUESTIONID} = 404 or {INSPQUESTION.QUESTIONID} = 1160 or {INSPQUESTION.QUESTIONID} = 1658 or {INSPQUESTION.QUESTIONID} = 2793 or {INSPQUESTION.QUESTIONID} = 2976 or {INSPQUESTION.QUESTIONID} = 3159 or {INSPQUESTION.QUESTIONID} = 3342)

then ToNumber ({INSPQUESTION.ANSWER})

Field #2 ==> FUEL_80

if ({INSPQUESTION.QUESTIONID} = 403 or {INSPQUESTION.QUESTIONID} = 1159 or {INSPQUESTION.QUESTIONID} = 1657 or {INSPQUESTION.QUESTIONID} = 2792 or {INSPQUESTION.QUESTIONID} = 2975 or {INSPQUESTION.QUESTIONID} = 3158 or {INSPQUESTION.QUESTIONID} = 3341)

then ToNumber ({INSPQUESTION.ANSWER})

The first field is working and has the proper value. The second field returns 0 and prints on the report.

I can not find any reason for one to work and the other to not function. Is there anyone that can lend me some insight on this?

Thank you