Jump to content
Sign in to follow this  
buckbuck

Case function, a result based on a text fragment

Recommended Posts

I am trying to define a field to produce a "text" result based on a "text fragment".

Case (

Description *"Home Depot" or

Description *"GENERAL PAINT"

;"Production"; )

This is a ledger. When my 'Description field contains the text "Home Depot" or General Paint" I know the field "Expense Type" will be a 'Production' expense. This automates a small part of my book keeping.

Thanks

Share this post


Link to post
Share on other sites

If you have different expense types, a better solution might be to set up a table with these types and flag the records accordingly, based on certain criteria. This would be a more robust solution than to search for description values that might change over time, or have typos.

In the meantime, try

Case (

PatternCount ( Description ; "Home Depot" ) or PatternCount ( Description ; "general paint" ) ;

"Production" )

Share this post


Link to post
Share on other sites

Eos

I am not sure what you accomplish by this. What I want to achieve are records with text based identifiers, ie; Production, Office, Utilities, Shipping etc.. This way the ledger can be summarized accordingly. Every month I import my bank statements. It just seemed a time saver to create this function since these suppliers are almost always classed or define as mentioned.

Is it possible to create this calculation?

Thanks

Share this post


Link to post
Share on other sites

Not sure what you mean; the calculation you are looking for is in my post.

Share this post


Link to post
Share on other sites

PatternCount as I understand it gives me a numeric count of the number of occurrences. I want a text result. Your calculation does not give me the result I am looking for.

Share this post


Link to post
Share on other sites

Are you trying to have Keywords for the records, Something like iPhoto?

Share this post


Link to post
Share on other sites

PatternCount as I understand it gives me a numeric count of the number of occurrences. I want a text result. Your calculation does not give me the result I am looking for.

Obviously your understanding of PatternCount is not complete. In this case, the calculation checks if one of the two strings appears in the other field; if so, it sets the result of the field to “Production“, or leaves it empty otherwise - which, if I recall correctly, is what you're after. The numeric result of PatternCount () is merely used as a Boolean truth value, and is not the final result of the field calculation.

Share this post


Link to post
Share on other sites

Your calculation does not give me the result I am looking for.

Why don't you provide a specific example - such as: the Description field contains "...", the calculation returns "..." instead of the expected "...".

Share this post


Link to post
Share on other sites

Also BuckBuck, the field should be type of CALCULATION set to text result and not auto-enter text (unless you need ability to over-ride the result). If you have existing data or sample text it won't change automatically if the field is text with auto-enter (do not replace). Is that why it appears not to work?

:-)

Share this post


Link to post
Share on other sites

Thank you for the suggestions. In the end PatternCount works however I needed to apply LaRetta's comment regarding 'auto complete'.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.