Comes up as 32.
That’s a space alright.
Getting back to this after testing it out–in case anyone else comes across this in the future–even an updateable BAQ won’t work because you cannot directly maintain the Supplier Part (PartXRefVend.VendPartNum) field. Changing it in any way simply creates a new supplier part record.
I must have misunderstood the question.
Are you trying to remove the leading spaces, or are you trying to update the rest of the data?
No, you didn’t misunderstand the question. Initially I was just going to update the lead time for these, but given how much of a pain it has proven to be, I’ve decided to “correct” each of these records so that I can DMT/updateable BAQ in the future to make updates. So my focus in testing the updateable BAQ was to see if I could update the Supplier Part through that method; I didn’t test it, but have complete confidence that other fields like lead time could be updated in that manner.
I’m testing right now if you could just blowout that table and reimport without the spaces.
I just blew out the table and reimported and everything is still hunky dory in my
supplier price list.
Now what I can’t tell you, is if any other records are directly referencing the old names with spaces.
could do a like ‘[ ]%’
The UBAQ should work for changing the other fields.
Agreed. I didn’t specifically test that, but I have no doubt that would work for the other fields.
I kind of sounds like some of the Epicor processes TRIM() the fields, but only certain ones.
If the value in the DB field has a leading space, and the DMT file’s field doesn’t, then it would be an ADD record, not an UPDATE. Therefore it would succeed. And you’d end up with two records. One with the leading space and one without.
What must be happening is that the DMT process finds the record that needs updating (even though it has a leading space), and passes all the fields from the DMT file. And instead of trying to update just the non-key fields, it tries to update them all - including the key field.
Normally this wouldn’t be an issue as the value in the DMT file exactly matches the existing field in the DB table. But whatever is saying that P/N
_ABC-123 == ABC-123 (where the underscore represents a space)
is the same record, fails when trying to change the key value _ABC-123 to ABC-123.
Can anyone answer that part?
If PartXRefVend.VendPartNum is not directly referenced anywhere else, he
can just blow out the table and re-import without spaces.
That sounds very SQLy. Or did you mean “delete the record through the UI and recreate it via DMT” ?
And that got me thinking… What does DMT do if you try to delete a record with a leading space in VendPartNum ?
No, I did mine with a baq and the db context, but could be done with the bo and deletebyid.
Ok cool, what did you do then ?
I went through the problem ones manually to clean them up, so that in the future I can just use the DMT because I no longer will have any with leading spaces (assuming my Buyers keep the data clean, like I’ve asked them to do). Bad data is a bit a pet peeve of mine, so I couldn’t just let it remain. To simply update the lead time, I could have used an updateable BAQ and moved on, leaving badly formatted records as they were.
1 Like
1 Like