Case expression not working.

[DDPMarc]

Hi all,

One more for today, I am trying to create a calculation for a field that creates a description. I know the calculation works exactly the way I want it to in a different program (filemaker pro) but I am having a hard time converting the syntax to AA.

This is what it looks like in FMP.

Case(IsEmpty(Name_First) and IsEmpty(Name_Last);Street_1; //result 1
IsEmpty ( Name_First) ;Name_Last & " " & Street_1 ; //result 2
IsEmpty (Name_Last) ;Name_First & " " & Street_1 ; //result 3
Name_First & " " & Name_Last & " " &Street_1 //default result
))

I can get as far as


CASE(ISNULL(NAME_FIRST).AND.ISNULL(NAME_LAST),STREET_1, //result 1
ISNULL(NAME_FIRST),NAME_LAST+" "+STREET_1, //result 2
ISNULL(NAME_LAST),NAME_FIRST+" "+STREET_1 //result 3


)



without any errors,l but cant figure out how to get the last default value or a fourth argument to work. I've tried

CASE(ISNULL(NAME_FIRST).AND.ISNULL(NAME_LAST),STREET_1, //result 1
ISNULL(NAME_FIRST),NAME_LAST+" "+STREET_1, //result 2
ISNULL(NAME_LAST),NAME_FIRST+" "+STREET_1, //result 3
ISNOTBLANK(NANE_FIRST).AND.ISNOTBLANK(NAME_LAST).AND.ISNOTBLANK(STREET_1),NAME_FIRST+" "+NAME_LAST+" "+STREET_1
)

It won't evaluate once I try the ISNOTBLANK which is a valid function (I think)

Also I have tried just using the top calculation that I can get to work (not give me an error msg) but it doesn't calculate on my form. How do I get it to automatically calculate as I fill in the three fields. I have created this calculation in the field rules on the tables tab but it doesn't auto calculate on my corresponding form.

I'm anxiously waiting for everything to start make sense in AA coming from FMP.

Thanks Again
Marc

[-Jinx-]

Re: Case expression not working.

It's not because you have NANE_FIRST instead of NAME_FIRST is it?
Other than that, I'm worthless on the desktop side of AA

[DDPMarc]

Re: Case expression not working.

No that is a typo, I hand typed the last bit because I couldn't get is to save in the expression builder with it that line in it.

[Al Buchholz]

Re: Case expression not working.

Are you trying to present name and address formatted properly when data is missing?

If so, look at a RTF (Rich Text Field) with a soft-return.

Otherwise let us know what you want the application to do, and we can suggest a way to accomplish it.

[DDPMarc]

Re: Case expression not working.

I'm trying to create an address description automatically based off of the contents of the three fields. Address_description = Name_first+" "+Name_Last+" "+Street_1
But I do not want the extra spaces if Name first is empty or last name is empty, hence the case statement.

I really don't know how to better describe what I'm trying to do than my first post it pretty much answers everything. I'm trying to create a calculated field that automatically calculates based on what is in the other tree fields.

[Tom Cone Jr]

Re: Case expression not working.

Working with native AA tables ( i.e. dbf format ) ?

[DDPMarc]

Re: Case expression not working.

Yes, native .dbf

[Al Buchholz]

Re: Case expression not working.

I'm trying to create an address description automatically based off of the contents of the three fields. Address_description = Name_first+" "+Name_Last+" "+Street_1
But I do not want the extra spaces if Name first is empty or last name is empty, hence the case statement.

I really don't know how to better describe what I'm trying to do than my first post it pretty much answers everything. I'm trying to create a calculated field that automatically calculates based on what is in the other tree fields.

So the context is to show the name and address nicely on a printed document? What I was asking for is what you are trying to accomplish with the technical solution that you are discussing. Usually technical solutions are driven by a non-technical need.

Using the RTF with calculated fields and soft-returns is what you are looking for. Way too hard to use a function for that job. Too many options and it gets confusing.

Every tool has it's nuances. For Alpha I use the RTF for exactly this purpose (and a couple of more).

In Crystal Reports, you'd use a text box with some calculated fields and click the suppress blank lines option.

Don't know Filemaker well enough to suggest a method for it. (Only for comparison purposes.) FM may not have an option, so you used the method you described.

Look at this section of the help file for detailed info...

http://wiki.alphasoftware.com/~alpha...ch+Text+Fields

[DDPMarc]

Re: Case expression not working.

Actually I need the result of the calculation stored in the database record. It will later at some point be used in a script to build an http_get URL to send the data in the URL and then consume the response, but this is later down the road.

As I said before I know the calculation works in FMP that is where i originally wrote it. I am transitioning from FMP to AA and want to convert a solution I made in FMP to AA. Once I get the initial solution built I am going to add a lot more function to it that otherwise would not be possible with FMP.

This is the very first calculation I have attempted to put together so it hasn't clicked just yet how to do it in AA. I've been using FMP for about 10 years now, I've only been using AA for about a week.

Way too hard to use a function for that job.

The documentation says that a case statement can evaluate an additional 15 expressions so I don't know how it would be to hard for this to work. In FMP it is lightning fast.

Any help, pointers or links to how to use calculated fields in a native dbf format for desktop would be appreciated. I'm pretty sure once I get a few fields calculating the way I want I'll be off and running, just like i was in FMP years ago.

[DDPMarc]

Re: Case expression not working.

Thank you all for attempting to help with this, I figured it out.

CASE(ISNULL(NAME_FIRST).AND.ISNULL(NAME_LAST),STREET_1,
ISNULL(NAME_FIRST),NAME_LAST+" "+STREET_1,
ISNULL(NAME_LAST),NAME_FIRST+" "+STREET_1,
ISNOTBLANK("name_first").AND.ISNOTBLANK("name_last").AND.ISNOTBLANK("street_1"),TRIM(NAME_FIRST)+" "+TRIM(NAME_LAST)+" "+TRIM(STREET_1))

Thanks
Marc

[Tom Cone Jr]

Re: Case expression not working.

A common malady.

the IsNotBlank() function takes a character argument, so the field name passed to the function must be enclosed in quotes.

ISNOTBLANK("Name_First")

NOT

ISNOTBLANK(Name_first)

Later I see you figured it out while I was typing! Good for you, and thanks for posting the final result. -- tom

[Al Buchholz]

Re: Case expression not working.

1. Then go the other way and build it step by step so you can see what works and what doesn't.

ISNULL() may not be a good choice since there may be a space value in the field and dbf's are not designed with NULL in mind.

2. Build your own UDF (User Defined Function) and build the result in a series of steps that uses an easier framework and no iteration limitation.

http://wiki.alphasoftware.com/~alpha...ined+Functions

the flow of the code would be something like this:

output=""

if first_name>"" then

   output = output + first_name

end if

if last_name>"" then

  if output>"" then

  output = output + " "+ last_name

  else 

  output = output + last_name

  end if

end if

'add crlf() if some part of the name is there.

  if output>"" then

  output = output + crlf()

  end if

[Tim Kiebert]

Re: Case expression not working.

Thank you all for attempting to help with this, I figured it out.

CASE(ISNULL(NAME_FIRST).AND.ISNULL(NAME_LAST),STREET_1,
ISNULL(NAME_FIRST),NAME_LAST+" "+STREET_1,
ISNULL(NAME_LAST),NAME_FIRST+" "+STREET_1,
ISNOTBLANK("name_first").AND.ISNOTBLANK("name_last").AND.ISNOTBLANK("street_1"),TRIM(NAME_FIRST)+" "+TRIM(NAME_LAST)+" "+TRIM(STREET_1))

Thanks
Marc

Hi Mark,
In the last test you don't really need to check the firstname and lastname fields because you have already deternined by this point (in the first line) that either are not blank. That leaves only testing for street_1, but earlier you don't test for street_1 being empty. If street_1 is empty you could end up with 1/a pass with an empty result, 2/a pass with only last name, 3/a pass with only first name, 4/ a fail - no result.

So if an empty result is allowable, which both line 1 and line 4 will give you, then you can accomplish the same thing with this...

LTRIM(NAME_FIRST-" "+NAME_LAST-" "+STREET_1-"")

See here for an explanation of the ' - ' http://wiki.alphasoftware.com/-+Conc...+Trim+Operator

[DDPMarc]

Re: Case expression not working.

@Tim that is definitely a much simpler solution, thanks.

@Al, that would be nice, I will definitely be looking into building UDF's I will need to find some more documentation on how to actually use the Create New Function dialog.

[Mike Wilson]

Re: Case expression not working.

Marc,
Creating a UDF is nothing more than a script enclosed into a FUNCTION - END FUNCTION with lots of added possibilities and benefits. I use almost exclusively UDFs. Go to the Code tab in control panel, select NEW from toolbar and chose FUNCTION from dropdown menu. Name the function 'say_hello' . Type in: msgbox("Hello Marc") between the FUNCTION - END FUNCTION, and then under the END FUNCTION type in - say_hello() Now either press the lightning bolt to run the function or save it and go to Interactive Window and type in say_hello() and then <enter>. This calls the UDF and the msgbox dialog with "Hello Mark" will show. You are now on your way using UDFs.