Sage 100

I have a customer on Version 2014 advanced with update 5. They use one of the views (vSalespersonCommissions) in an ODBC query to Excel as a basis for their commission reporting. Somehow the view definition got changed and fields are missing from what comes standard in Sage 100. There was a user defined field added to AR_Customer table and it appears when that was added that all of the invoice history tables that are used in that view were removed. Does anyone know of a way to rebuild just the one view?

Tim, can't you do a clean install on your workstation, patch to the same level as the customer, and then script the view. With the script, you could recreate the view in your customers environment. You would have to add the UDF's manually afterwards. I don't know how many UDF;s are involved but I assume they aren't onerous.

Hey Shawn - Not sure what you mean about scripting the view. Can you clarify?

In SSMS, you can export the definition of any object into a script file that cab executed to recreate the object. Joe can show you.

Shawn, This is sage 100 advanced so unfortunately using SQL management studio isn't an option.

I'm sorry Tim. I read your post from my cell phone and though your message referenced premium.

Tim - You could try this view export / import utility built into MAS. It may work (it may not). This is what Sage used themselves to add the views to the dictionary. ON YOUR OWN v2014 SYSTEM: 1) Create the same exact UDFs the client has for these tables: AR_Salesperson AR_SalespersonCommission AR_Customer AR_InvoiceHistoryHeader These 4 tables makeup the vSalespersonCommissions view. It doesn't include all columns from all tables but it does include all UDFs from all 4 tables. Also anytime you add a new UDF the view gets updated to include it. 2) Go to File / Run / SYZCON At the prompt type this this: RUN ""*VIEWS/VIEWS"" 3) The Data Source Maintenance window should appear. 4) Click on vSalespersonCommissions then click Properties button. The Define a View window will appear. 5) For each of the 4 tables uncheck UDFs that belong to you but are not on client's system. Then click Okay. 6) Now click Export button. The Export View and Data Source Definitions window should appear. Expand the Show All option and find the group called Salesperson Information. 7) Expand that and check vSalespersonCommissions view. 8) Complete the export CLIENT SIDE: 9) Have all users exit out of client system 10) On client system MAKE A BACKUP of at least their 4 tables above, ProvideX.* file in \mas90, and the pvxview.* files in \mas90. Or just backup the whole damn \mas90 folder. Or instead of Steps 9 and 10, make a copy of the \mas90 folder and try the import on Step 12 into the copy system. If it works repeat on live system. 11) Do Step 6 above but this time choose Import and attempt the import. 12) If import works w/o error, obviously start up MAS and check things out. The export / import should have MERGED your view into client's dictionary BUT make sure they can do their Excel query and that no UDFs were clobbered in the process (that was a PSA). Even better make them responsible for checking it.

Thanks Alnoor. I will give it a try over the weekend when they are not working.