×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Editing Fields and their corresponding columns

thread639-1775226

Editing Fields and their corresponding columns

Editing Fields and their corresponding columns

(OP)
I'm having trouble finding information on how to use the designer in Macola 9.6.900. On the order entry screen my old boss put in 2 fields for Cash Receipts but he used user_def_fld_3\4 for those cash values. This is making it very difficult for us to report\calculate on those because those fields are char(30), not decimal or numeric. I wanted to change those fields (or replace them) with fields that tie in to extra_10 and extra_11, both decimal(16,2), in the oehdrhst_sql table. Can anyone point me in the right direction on how to accomplish this?

RE: Editing Fields and their corresponding columns

I do not believe there is a way to do this with screen designer. You can do it with some Flexibility however. Do you know VBA?

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Macola 10, Synergy, and Crystal Reports.
Check out our Macola tools:
www.gainfocus.biz/exceladdin.html

RE: Editing Fields and their corresponding columns

(OP)
Unfortunately I have very little coding experience at all.

RE: Editing Fields and their corresponding columns

Sorry for the late reply. Do you own Flexibility? I am not asking about your coding experience.

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Macola 10, Synergy, and Crystal Reports.
Check out our Macola tools:
www.gainfocus.biz/exceladdin.html

RE: Editing Fields and their corresponding columns

One way to get around this would be to create a SQL view against your oehdrhst_sql table to convert the user_def_fld CHAR values to real numbers. For example;

CODE --> SQL

CASE WHEN ISNULL(SUBSTRING(oh.user_def_fld_5,2,16),'0') NOT LIKE '%[^0-9]%'
		THEN				
			(CAST(ISNULL(SUBSTRING(oh.user_def_fld_5,2,16),'0') AS DECIMAL) / 100)
		ELSE 0 END AS user_def_fld_5_decimal

You can leave the CASE statement out if you are always populating the field with values that convert cleanly.

This would need to be adjusted depending on how the fields are defined on the screen e.g. currency, decimal etc.

Peter Shirley
Macola Consultant, PA and surrounding states.
http://www.erpessentials.com

RE: Editing Fields and their corresponding columns

(OP)
@dgillz, I don't own flexibility.

@crystalrporting, I've tried casting as and converting but I always get "Error converting data type varchar to numeric." Using exactly your code all the numeric values converted to decimal come out as 0.000000.

RE: Editing Fields and their corresponding columns

How is your field designed within the screen designer? For example decimal, currency etc.

Macola stores these fields differently depending on how they are defined on the screen - you will likely need to adjust the position in the SQL SUBSTRING to ensure you get the correct values.

Peter Shirley
Macola Consultant, PA and surrounding states.
http://www.erpessentials.com

RE: Editing Fields and their corresponding columns

(OP)
Is this what you're asking for?


RE: Editing Fields and their corresponding columns

Perfect. So even though you are putting numeric values in that field, whoever set it up originally left the field defined as Alpha. That means any values could be stored in that field in the database. You would need to create a SQL CASE statement that would take all combinations into account. The example I provided was designed for a field that had been predefined on the screen as Currency so only NULL or currency format values would be expected in the database.

Peter Shirley
Macola Consultant, PA and surrounding states.
http://www.erpessentials.com

RE: Editing Fields and their corresponding columns

(OP)
Would it make sense to change those fields to currency now? those user defined fields aren't being used elsewhere.

RE: Editing Fields and their corresponding columns

You cannot change those fields to a different data type. Peter's suggestion is a good one. If you don't know how to write a SQL CASE statement I am sure Peter or another consultant can do it for you.

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Macola 10, Synergy, and Crystal Reports.
Check out our Macola tools:
www.gainfocus.biz/exceladdin.html

RE: Editing Fields and their corresponding columns

You could change the field type now - to Currency or Decimal etc. This doesn't change the type of the database field - it remains alpha. What it would do is force any new entries into that user defined field to be in a consistent format. The consistent format makes extrapolating the alpha field value into a numeric value in SQL much easier. Keep in mind that any entries made prior to you changing the field type will still be in an inconsistent format.

Peter Shirley
Macola Consultant, PA and surrounding states.
http://www.erpessentials.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close