Using IF function

[Alexandre Cunha]

Hi, Brendan!

I have felt a great need for an IF function to be able to determine the value of a calculated field.

I’ll give you an example:

I have a field with the value of an expense / revenue whose value is always positive. I need to create a calculation so that this value is multiplied by (-1) if it is an account payable and maintained as it is if it is an account receivable. In this case, the conditional test portion of my IF function would be [Type = “Payable”]. I ask: is it possible to use the IF function (X, Y, Z) in this case?

Is there a forecast for a function like the one I need on the way soon?

Thank you!

Alexandre Cunha

[Brendan]

Hi Alexandre,

Right now the conditional part of the IF(X,Y,Z) function requires the X value to be numeric. But it can return a string if required.

I’ll have to see if my math parser code is compatible with strings for the conditional part of the function. Right now it’s not so would need to be modified to support it, if that’s even possible without too much trouble.

[Mike Schwartz]

Alexandre,

It looks like you’re using a Text field named “Type” that either has a value of “Payable” or “Receivable”. The IF(X,Y,Z) function currently won’t handle the test you desire, Type=”Payable”.

Instead of using a Text field for “Type”, you could create two Check Mark fields named “Payable” and “Receivable”, and check the one that’s appropriate for each record. Then your calculation field could have this formula: IF(Payable, -1*Number, Number). If you still need a field that contains either the string “Payable” or the string “Receivable”, you can add a second Calculation field that returns a text value: IF(Payable,”Payable”,”Receivable”).

Hope that helps,
Mike

[Alexandre Cunha]

Hello, Mike!

It worked perfectly, very clever this idea!

Taking advantage, sorry, is there a way, based on the Check Mark field option, forcing the value of a list option in a text field?

I explain better:

If the “Payable” field is “Yes”, then the “Type” text field would be forced to be equal an entry in the “Types of Account” list. I need this to be able to keep the highlight color in “Record Color Field” for this Form.

I hope I have been clear in describing the problem.
Thank you for your valuable help so far.

Alexandre

[Mike Schwartz]

Alexandre,

If I understand you correctly, you currently have this set-up: You have a text field called “Type of Account” that is configured to use a pick list that you have defined, let’s call it “Account Types”. Your Account Types pick list has maybe two values, “Payable” and “Receivable”, and in the pick list you have assigned colors, maybe Green for “Receivable” and Red for “Payable”. Then, on the form definition you selected “Type of Account” for the Record Color Field.

Now you would like to use the new check box field to force a value in your “Type of Account” field, in order to preserve the highlight colors in the record list. I do not believe that Tap Forms currently supports that. Only a calculation field can take a value based upon the values of other fields. And you cannot assign a pick list to a calculation field. In my previous post I mentioned how you can define a calculation field to display the values “Payable” and “Receivable” based on the check marks, but that won’t achieve your goal to preserve the color highlighting.

For now, I think that the only thing you can do is to have both the Check Mark field and a Text/picklist field on your form. The Check Mark field allows the numerical calculation for the negative values, and the Text field allows the record color highlighting.

The down-side of this approach is the possibility for introducing errors if the checkbox and text fields do not match. I gave some thought to how you could build-in some error testing. If you have two separate check-boxes for “Payable” and “Receivable”, then only one should be checked. If neither one is checked, or if both are checked, it is an error. Because check boxes are stored internally as the value 0 or 1, you can simply calculate the sum of Payable+Receivable, and the result will be 0, 1, or 2. If the sum equals 0 or 2, there is an error. You can craft an error test around that. The harder part is testing that the check boxes match the Text field. You can’t test that with an IF statement — that was the original limitation that got us to use check boxes in the first place!

Hope that helps,
— Mike

[Mike Schwartz]

Also, keep in mind that if the purpose of the color highlighting is to allow you to quickly identify, say, records that are Payable, you can accomplish the same thing with a Saved Search.

— Mike

[Alexandre Cunha]

Hi again Mike,

You understood my question correctly. That was exactly my doubt.

Unfortunately, TF is not able to meet this need. Let’s wait for new versions that should include new functions. I’m looking forward to it soon … I really enjoyed Tap Forms and hardly believe in its evolution.

I was not able to carry out your idea of mounting an error test with the function SUM (X, Y, Z, …), since it was not possible to test its result with the function IF (X,Y,Z). I’m stunned … hehehe …

Again, thank you for your commitment to my needs!

Alexandre

[Mike Schwartz]

Alexandre,

The following function worked for me:

IF(Payable+Receivable-1,”ERROR”,”OK”), where Payable and Receivable are the two Check Mark fields. NB: The Result Type must be set to “Text”.

I’m still thinking about how you might go about testing a check mark field against a text field.

— Mike

[Alexandre Cunha]

Excellent, Mike!

It worked very well now that you pointed me the right way. Sometimes my English betrays me … hehehe …

Here’s what I did:

I created a new field just below the two check marks, called “Message”, and I used your hint with the
IF(X,Y,Z) function… it worked as a system error message line … so much cool!

To be perfect at all, it would be good if I could hide/show the field and its name if there was no error to highlight.. But that can not (still) be used in TF, right?

Again, thank you very much for your commitment to my requests. I really appreciate this.

Alexandre

[Mike Schwartz]

If you are using a custom layout, then do the following:

1. Change the Message function from IF(Payable+Receivable-1,”ERROR”,”OK”) to IF(Payable+Receivable-1,”ERROR,””), so it returns a null string instead of “OK”.
2. Edit the Message field in the layout: uncheck “Stroke Color” and “Fill Color”.
3. Delete the Message field label from your layout — you don’t really need it.

Doing the above will make the field invisible except when its value is “ERROR”. This will only work on a custom layout — you will always see the field in the Default Layout.

Hope that helps,
Mike

[Alexandre Cunha]

Hi, Mike!

I had already thought that using Custom Layouts I would have more alternatives, but I confess that I am in the initial stage of learning about TF and I have not yet dared to leave the “Default” … a little more time and I will get there !

You said: “I’m still thinking about how you might go about testing a check mark field against a text field”.

I’ll be waiting. I hope you get it but it’s not that urgent any more, because of the alternative solutions you indicated to me… they worked very well.

Thanks a lot for your support,

Alexandre

[Author]

Posts