Hi all,
I just got this all figured out and figured I'd document this here for future me and anyone else who might need it.
I typically use the Sage 50 .dat file documentation when writing SQL queries in the connector: https://help-sage50.na.sage.com/en-us/2019/Content/DDFs/A_List_of_Data_Files.htm
However, there are some missing tables that are not included in that list that are very necessary to use. For instance, InventoryCosts and JrnlSNum are two that I've personally ran across.
A tip for finding tables that might not be listed there is to, inside of the Connector too, start an SQL query and then click Insert --> Insert Table. This will show you all available tables.
I am trying to write a query that will show all of our serialized items. Since there is no documentation for the JrnlSNum table here is what I've found:
TransactionClass - This is an integer from 0 - 5 in our system and indicates the type of transaction.
0 - Added into system via Work Ticket or Assembly
1 - Added into system via Purchase Journal or Inventory Adjustment
2 - Added into system via a negative quantity on a Sales Journal or in a Credit Memo
3 - Removed from system by Sales Journal
4 - Removed from system by Vendor Credit Memo
5 - Removed from system by Inventory Adjustment