Editing Fields and their corresponding columns

[ndininno]

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?

 

[dgillz]

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:

http://www.gainfocus.biz/exceladdin.html

 

[ndininno]

Unfortunately I have very little coding experience at all.

 

[dgillz]

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:

http://www.gainfocus.biz/exceladdin.html

 

[crystalreporting]

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;

 	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

 

[ndininno]

@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.

 

[crystalreporting]

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

 

[ndininno]

Is this what you're asking for?

 

[crystalreporting]

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

 

[ndininno]

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

 

[dgillz]

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:

http://www.gainfocus.biz/exceladdin.html

 

[crystalreporting]

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