Sage 100

Another MAS90 4.3 to MAS200 SQL 4.5.3 issue. Numeric PO header UDF was converted to SQL and the definition in SQL has 0 decimal places. The mask on the UDF in 4.3 and in the converted 4.5 for that matter has 2 decimal places. The exisitng data has and we use 2 decimal places. Anyone seen anything like this or have an idea as to what i can do about it?

You can fix the field definition in SQL and remigrate (or create an SSIS job to just migrate that field). And send Sage a bug report and a bill for doing their QC for them ;-)

LOL! Well...that's at least part of my issue. I try to change the field definition in SQL and it won't let me. I get something to the effect that (Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.)

That's one of those ""so secure I can't get any work done"" changes M$ loves to make. See this article for how to turn it off: http://stackoverflow.com/questions/6810425/saving-change-is-not-permitted-in-sql-server

Thanks Phil, I can't believe I didn't google that myself. A couple- hmmm i wonders: 1) will this get overwritten when I re-migrate? 2) will the sql drop/recreate retain the current data?

It's based on what's in the dictionary (providex.dde) not what's in the mask in Customizer Selection or what's in the physical pvx data. Will show you the steps once I get some to respond properly. Yes it will happen again if you don't fix the dict

GREAT! Thanks Alnoor. What kind of response are you looking for?

I meant time to respond .. let me get off this call

OK, that makes sense, lol. Take your time, no hurry.

The SQL drop/recreate should not lose any data, but backing up before making structural changes is always a good idea.

Your data actually first converts to 4.50 pvx and then in a subsequent process to 4.50 SQL. But the dictionary structure (providex.* files) is pretty much stays the same between the two. Usually numeric UDFs are hard-coded for 2 decimal places in the dict even if the masking is set to 3. The normal complaint is my 3-digit numeric UDF got rounded off to 2 in SQL so yours going from 2 to 0 is certainly strange. Here are 2 places to look at: On 4.50 SQL: 1. DFDM Providex.ddf 2. Change Key No to ByName 3. Select PO_PurchaseOrderHeader 4. Change the Key No back to Primary Key and copy into clipboard what it says for Key= in top left 5. DFDM Providex.dde and paste in the Key 6. Now use the Move Next button (Ctrl-F7) to keep moving through all the fields in PO_PurchaseOrderHeader until you find your UDF_blahblah 7. What does it say for both Field 05 and Field 09. On 4.30: 1. DFDM CM_UDF.m4t 2. Find the POH key with your numeric field 3. What does it say for Field 13 MaskValue and Field 22 MaxLength

Thanks Alnoor and Phil, I've been temporarily pulled to something else i'll report back a little later.

@AlnoorCassim sorry it took so long 4.5 field 05 is 15.00 field 09 is #,###,###.00

4.3 is the same. Field 13 mask is same as 4.5 Field 22 maxLength is 15.00

Ok that explains what happened to you but the why is still a mystery. The .00 portion of 15.00 in Field 5 of 4.50 means the SQL conversion will round your UDF to 0 decimal places. When I look at mine it say 10.20 which means round to 2 decimals. Can you look at 1 more place in 4.30 to see if this might be the source of the problem: On 4.30: * Use same steps above to go through DDF and DDE but make your table name in DDF be this: PO_90_UDF_PurchaseOrder * Then get the Primary Key and trace that to the UDF record in DDE * Now what does it say for Field 5 (wondering if it says 15.00 )

Interesting...field 5 is 10.20 in the 4.3 DDE file. ( FYI, i have several UDFs in that file and there are 4 numeric ones that are all doing the same thing. rounding to 0 decimal places. All 4 are 10.20 in this file.)

I have the same issue. I changed the v5.1 premium providex.dde field 5 from 15.0 to 10.2. Do I need to do anything after that?