Sage 100

customer on 4.5.5 Trying to export the GL ACCT key from Vendor Master file with VI. The only results I can get is a preformatted gl number and not the raw number. When you look thru DFDM the field is what you expect ######### however based on your gl segmentation my results are ####-##-## . That would be ok but when you go to reimport it is using the mask as part of the import record without you being able to only replace the last segment. ex 1503-00-00 needs to be 1503-00-01 there seems to be no way to reference the 01. Is this broke?

There is a ""MID"" command in VI, so you can reference it as mid(IMPORTED_FIELD,1,8)+""01

OK, but how would you use this, it looks like only option is conditional or default and I have tried both with the replace function and VI does not like it.

First you have a TEMP FIELD importing that value. Then you have the GL Account field and it is a CALCULATED field and the calculation is mid(TEMP001,1,8)+""01"". If I'm reading you right....

I will give that a try thanks

Error #26: Variable type invalid AP_Vendor.AccountKey$=mid(TEMP001,1,8)+""01"". I get this just putting this the mid(TEM line in the calculation field not even getting to execute I have tried {AP_Vendor.AccountKey$}=mid(TEMP001,1,8)+""01"" as well as mid(TEMP001,1,8)+""01"" It is almost like it wants it both ways no formatting but it only references that I am not used to MAS90 giving you a formatted field when working with VI it ususally works with the RAW data.

Make sure the TEMP001 field is String not Numeric.

It was. I appreciate your help. I have never had this much trouble importing what should be a simple replace. I even tried isolating about 20 of these accounts and just doing a wholesale replace and the results say successful but it changes nothing in the record. I am certainly baffled.

Email me your import and I'll take a look at it.

So why not just use an Excel query to get the information? Very simple to do. Honestly, I have used VI to export for six or seven years now. Just too damn hard to get consistent data. Was the straw that broke the camels back. I forced myself to learn Excel Queries and have never looked back....

But the Excel querie won't do an import!

But your issue is with an EXPORT with VI. I do imports with VI all the time, but NEVER export. What am I missing here?

I think the question is how to parse it to import it with the last segment as 01 instead of 00.

Oh, I get it. I think Beth's suggestion should work. In Excel, you could do an @left(a1,8)&""01"" to get the new import. You may also be having issues in that the AP_Vendor.AccountKey$ may need to be a calculated field so that MAS imports the formatted field as a calculation. Whenever I import a formatted GL account, I always do it as a calculated temp field.

@JeffSchwenk you are right on . The problem I am having is that seeing that the export puts it in a formatted field. When you go to import it still thinks you are using a formated field even when I am not. And if you try to go that route, it won't allow 10 characters so it won't import. I tried just putting in a 9 character number as a test and it says it passed but when you look at data it doesn't change. Can someone verify that with ver 4.5.5 you can import different number into Vendor GL. I do imports all the time and cannont ever remember having to pre format this. Thanks to everyone's input.

oh and the reason I reallly wanted this to work is there are almost 2000 of these that will take forever manually:(

Click the ""Skip Data Source Truncation"" on the Configuration tab. That's why it won't take your 10 characters.

@BethBowers wins the prize. It was the data truncation on the config tab. Never run into that before so I learned something workin on the weekend. Thanks also to @JeffSchwenk and @RobertWood . I sure didn't want to manually change those suckers! Thanks again Beth

What goes around comes around - I learned about it here from someone else's problem a while back!

Thanks @BethBowers and @RichardBryant I saw this posting last week and now I needed it too!

I am glad my problem helped someone else!! Thanks again to @BethBowers who stayed with me while we figured this out.