I need a formula that references an empty field to not display as zero.

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • Answered
I need a formula that references an empty field to not display as zero.

Here is a bigger picture of what I've got going on to hopefully clarify what i'm looking for. I have parts checkout table that has a table to table relationship with a master parts list table. I've made multiple reports from this mast part list table where each "Item" has a report to list multiple serial numbers under that Item calcification.

 When the user is going to check out a part in the parts check out table, they select a radio button for a part type and then a drop down list will source serial numbers from one of the previously built reports. For example, by selecting "Related Item 1", "Related Item 2",or "Related Item 3" a different list of serial numbers would show available. For each transaction, only one item / serial number is able to be selected. So if i choose Item 2 and pick a serial number from that filed, then Item 1 and Item 3 would be blank.

 I have report in the check out table that is a mast list of all the check outs. Instead of seeing "Related Item 1", "Related Item 2", and "Related Item 3" columns on the report where two of the three columns would be blank, i want to make a field that would combine all of these related item into one formula text field to display a serial number regardless which Related Item is selected. I've tried a few options but the empty fields keep displaying zeros. Can i please get some of your expertise on combining these fields without having the empty spaces display as zeros?
Photo of Brian

Brian

  • 200 Points 100 badge 2x thumb

Posted 5 months ago

  • 0
  • 1
Try this

List(" ", 
IF([Related Item 1]>0, [Related Item 1]),
IF([Related Item 2]>0, [Related Item 2]),
IF([Related Item 3]>0, [Related Item 3]))


Photo of Brian

Brian

  • 200 Points 100 badge 2x thumb
Thanks for you help. i received a formula error saying that it was looking for "text" for the second "Related Item" part of the formula. Next i tried to convert the related item item fields to text and replaced the {related item} field with my converted to text fields but then the error was that it was looking for a number. Finally i combined the fields and it worked .

List(" ", 
IF([Related Item3]>0, [Rel3 Item to text]),
IF([Related Item4]>0, [Rel4 Item to text]),
IF([Related Item5]>0, [Rel5 Item to text]),

IF([Related Item6]>0, [Rel6 Item to text]),

IF([Related Item7]>0, [Rel7 Item to text]),

IF([Related Item8]>0, [Rel8 Item to text]))

I appreciate getting my on the right path
Photo of AustinK

AustinK

  • 2,214 Points 2k badge 2x thumb
For the future you can do something like this instead which should save you some work.

IF([Related Item 1]>0, ToText([Related Item 1]))

There are many more like ToText that you can use to change a field in a formula.