How to find the notes master table (SY03900) in GP SmartList Builder

Question Status

Suggested Answer
Barbara Salazar asked a question on 6 Jan 2012 4:12 PM

With SmartList Builder in Great Plains: How to find the notes master table (SY03900).  I'm trying to look under Sales in Great Plains and so far, no luck... Thanks a lot!

I was advised that the notes master table (SY03900) contains customer notes created in Customer Maintenance Window. I need to these customer notes to Smartlist.

Reply
Barbara Salazar responded on 6 Jan 2012 4:47 PM

Just to be specific, I'm asking what do I need to select for "Product" and "Series" in SmartListBuilder in Great Plains in order to get customer notes that are created in Customer Maintenance Window?

Reply
callen responded on 6 Jan 2012 7:59 PM

You will need to look under Microsoft Dynamics GP as the product and company as the series to find Record Notes Master.

However, you don't need to add the table just to see the notes.  Find the Note Index column in the RM Customer MSTR table.  Click on it and then click the blue arrow up above to change the format of the field. You should see a checkbox that says "Note Index - Show Note" and it should be marked automatically.  

Reply
Natarajan SankaraNarayanan responded on 6 Mar 2013 3:34 AM

Hi Barbara,

Cellan is right,  or u can check that table(SY03900) under Sql Table Security  to create a new smartlist to see the customer notes

Thanks,

Nataraj S

Reply
anandk responded on 17 Feb 2017 10:38 AM

Hi Callen,

Want to create a smartlist for Notes appearing next to the Document No. under Sales Transaction Entry Screen under Doc Type/Type ID "Invoice". What is the table that needs to linked to the Record Notes Master. Thanks

Anand

Reply
Suggested Answer
Harry Lee responded on 17 Feb 2017 12:19 PM

Barbara,

I am not sure what your intention is, but the SY03900 table holds the notes at the company level in Dynamics GP. Notes on various master records and documents, are stored as an index reference in Master and Transaction tables.  So, when looking for the Order Notes on a Sales Transaction, what you are looking for is two things.  You need the NOTEINDX field the transaction table.

SOOH  in the query above is and Alias for the Sales Order Open Header table or SOP10100.

You could use this value to link to the NOTEINDX in the SY03900 table and return the Note Text, but you don't have to because SmarList builder has a feature that you can use to return note text from GP based on the Note Index value.  I have outlined this below.

Feel free to ask any follow up questions you might have.

Reply
Rosemary responded on 17 Feb 2017 1:30 PM

When notes are added it can be accessed even after a document has been posted.

Is there an option to prevent it from being deleted.

Our Receivables department adds to their notes on a customer, but these can be deleted.

Regards

Rosemary

Reply
callen responded on 17 Feb 2017 1:38 PM

You cannot prevent these notes from being deleted, unfortunately. You would need to use a tool like Extender which enables you to enter notes but prevent their deletion.

If you don't have SmartList Builder, you can create a SQL view that has the note text and then use SmartList Designer to build a SmartList.

Reply
Suggested Answer
Harry Lee responded on 17 Feb 2017 1:56 PM

Rosemary,

Willoware has a Note Locking tool available as an add-on. It is part of their GP Power Pack product.

willoware.com/gp-powerpack-note-locking

Reply
Rosemary responded on 3 Mar 2017 11:18 AM

Thanks very much for that info.

Regards

Reply
Leslie Vail responded on 4 Mar 2017 1:51 AM

Hi Rosemary,

Did you get this all worked out?

Leslie

Reply
Suggested Answer
Harry Lee responded on 17 Feb 2017 12:19 PM

Barbara,

I am not sure what your intention is, but the SY03900 table holds the notes at the company level in Dynamics GP. Notes on various master records and documents, are stored as an index reference in Master and Transaction tables.  So, when looking for the Order Notes on a Sales Transaction, what you are looking for is two things.  You need the NOTEINDX field the transaction table.

SOOH  in the query above is and Alias for the Sales Order Open Header table or SOP10100.

You could use this value to link to the NOTEINDX in the SY03900 table and return the Note Text, but you don't have to because SmarList builder has a feature that you can use to return note text from GP based on the Note Index value.  I have outlined this below.

Feel free to ask any follow up questions you might have.

Reply
Suggested Answer
Harry Lee responded on 17 Feb 2017 1:56 PM

Rosemary,

Willoware has a Note Locking tool available as an add-on. It is part of their GP Power Pack product.

willoware.com/gp-powerpack-note-locking

Reply