Add fields to Sales Invoice form - Is VBA Modifier the solution?

(0) Share

Report

Posted on by Accounting Department

1,552

Hi. We have a request to be able to track a second quantity and Unit of Measure on an Invoice Line. This information comes from another system, and we eventually want to import from that system into GP using an integration (eConnect).

For example we sell a product by weight, but also want to track number of packages. There is no equivalency setup, because 10 Packages could be 500 KG or 525 KG or 499 KG. We just want to be able to track both for reporting purposes.

Is VBA Modifier the solution for this or are there other ways we can do this?

With VBA Modifier can we actually create fields in GP tables? I don't know how this works.

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

L Vail 65,271 on at

Like (0)

Report

Hi barmeh,

You can use VBA to make an external (ADO) connection to the GP tables, but you would just be slamming data in and not get the benefit of the business logic. There is, however, a User table created by GP just for you. It's called the DUOS table. It's table SY90000 in the company database. The VBA Developer's Guide (VBADevelopersGuide.pdf) in the Documentation folder explains exactly how to populate this table. Using this table, you can collect additional information using the existing database structure.

If you have a lot of information, this might not work so well, but it's worth a look.

There are many ways to do what you are wanting. I was just trying to come up with something that might not require any 'outside' data objects.

I feel certain that other solutions are on the way. Stay tuned :)

Kind regards,

Leslie

Was this reply helpful? Yes No
sandipdjadhav 18,306 on at

Like (0)

Report

Hello,

Already you got great suggession from Leslie. I will recommend to create another table and the details with Invoice # plus Line Items details with additonal requirement.

This way you are not going to messed up with existing GP logic. Create external report with all other details.

Thanks

Sandip

Was this reply helpful? Yes No
Accounting Department 1,552 on at

Like (0)

Report

Thanks to both suggestions. If I understand correctly,you are both suggesting to use the default method that VBA Modifier offers? That being the DUOS tables? And the data would get into those tables by having extra fields on the Sales Transaction Entry window to capture the add'l information? I am less concerned with integration and more with having the ability to enter the data, for now. The development for integration will come later.

Was this reply helpful? Yes No
Suggested answer

David Musgrave MVP ... 14,095 Most Valuable Professional on at

Like (0)

Report

Hi Barb

As VBA (Visual Basic for Applications) has reached end of life and is not compatible with newer operating systems, versions of office and the web client, I would recommend against using it.

I would still use Modifier to add the fields to the windows, and use the DUOS (Dynamic User Object Store) SY90000 table to store the data, but I would use GP Power Tools - Developer Tools module to add the business logic.

See the following articles (including step by step example and video) and benefits presentation:

winthropdc.wordpress.com/.../

winthropdc.wordpress.com/.../

Benefits Presentation: www.winthropdc.com/.../GPPowerTools_Benefits.ppsx

GPPT Portal: http://winthropdc.com/GPPT

Regards

David

Was this reply helpful? Yes No
DebCummings 75 on at

Like (0)

Report

We modify our invoices using the Report Writer - after we had added the fields we wanted.

Was this reply helpful? Yes No