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?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Editing Fields and their corresponding columns
- Thread starter ndininno
- Start date
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:
Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Macola 10, Synergy, and Crystal Reports.
Check out our Macola tools:
- Thread starter
- #3
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:
Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Macola 10, Synergy, and Crystal Reports.
Check out our Macola tools:
crystalreporting
Instructor
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;
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.
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_decimalYou 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.
- Thread starter
- #6
@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.
@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
Instructor
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.
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.
- Thread starter
- #8
crystalreporting
Instructor
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.
Peter Shirley
Macola Consultant, PA and surrounding states.
- Thread starter
- #10
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:
Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Macola 10, Synergy, and Crystal Reports.
Check out our Macola tools:
crystalreporting
Instructor
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.
Peter Shirley
Macola Consultant, PA and surrounding states.
- Status