Sage 100

I've seen a post or 2 on here regarding this, but I don't think any solutions were ever provided.  

Customer wants to see which employees have differing email addresses between PR_Employee and PR_EmployeeDocuments.  Looking at EmployeeDocuments, no record contains an email address, though it can clearly be seen on the screen when clicking the Paperless button from Employee maintenance.  Relevant field is not in the PR_EmployeePIIWrk table and the field comes across blank in my custom report in Sage (which does have Employee PII included as it's pulling PR_Employee info.

Any ideas on how to get this data out?  Or is it just an encrypted field that we do not have access to?

------------------------------
Chris Love
Accounting Systems, Inc. (ASI)
------------------------------

if you add the report as a custom report on the reports menu you can have the PII field added., just join it via the employee key..  When  you add it to the menu via the  custom wizard it will get you the option to add the PR_Employee_PII table and all the data will be available,

------------------------------
Jim Woodhead
DSD Business Systems
619-990-3946
------------------------------

I was thinking a VI Export might work... if the table you want doesn't have a custom report PII table like Jim is suggesting.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
------------------------------

Original Message:
Sent: 01-10-2023 16:55
From: Jim Woodhead
Subject: PR Employee Documents - Email Address

if you add the report as a custom report on the reports menu you can have the PII field added., just join it via the employee key..  When  you add it to the menu via the  custom wizard it will get you the option to add the PR_Employee_PII table and all the data will be available,

------------------------------
Jim Woodhead
DSD Business Systems
619-990-3946
------------------------------

That gives me PR_Employee.EmailAddress, but I need PR_EmployeeDocuments.EmailAddress

------------------------------
Chris Love
Accounting Systems, Inc. (ASI)
------------------------------

Original Message:
Sent: 01-10-2023 16:55
From: Jim Woodhead
Subject: PR Employee Documents - Email Address

if you add the report as a custom report on the reports menu you can have the PII field added., just join it via the employee key..  When  you add it to the menu via the  custom wizard it will get you the option to add the PR_Employee_PII table and all the data will be available,

------------------------------
Jim Woodhead
DSD Business Systems
619-990-3946
------------------------------

That field (PR_EmployeeDocuments.EmailAddress) is loaded unencrypted into the PR_EmployeeListingWrk table. One can link that table by EmployeeKey and filtering  records where Document="Check" and "TaxGroup" = "". I have a script solution that I sell very reasonably that will automate the access to an up-to-date version of this table inside or outside of Sage. You can test if it will work for you by putting the Employee Listing in preview using the setting for "Report Type to Print" as "Employee Information" and checking the option for "Paperless Office Delivery". If you're dealing with Adv/Std, note the session number (that's the numeric digits in the form USRMMDDYYnnnnnn at the end of the work file e.g. PR_EmployeeListingWrkUSRMMDDYYnnnnnn.M4T) and putting that into the "Session ID" field of the ODBC control panel of SOTAMAS90. If its Premium you can check out this thread link.  If this works for you contact me if you would like it automated.

------------------------------
Dan Burleson
Software Consultant
Connex Software
------------------------------

Original Message:
Sent: 01-10-2023 17:04
From: Chris Love
Subject: PR Employee Documents - Email Address

That gives me PR_Employee.EmailAddress, but I need PR_EmployeeDocuments.EmailAddress

------------------------------
Chris Love
Accounting Systems, Inc. (ASI)
------------------------------


Original Message:
Sent: 01-10-2023 16:55
From: Jim Woodhead
Subject: PR Employee Documents - Email Address

if you add the report as a custom report on the reports menu you can have the PII field added., just join it via the employee key..  When  you add it to the menu via the  custom wizard it will get you the option to add the PR_Employee_PII table and all the data will be available,

------------------------------
Jim Woodhead
DSD Business Systems
619-990-3946

@Dan Burleson mentioned his solution which is a must have for anyone doing external queries in the PR database.  I have been using it for several years and is well worth the nominal cost.

------------------------------
Jeff Schwenk
Bottomline Software, Inc.
(540) 221-4444
------------------------------

Original Message:
Sent: 01-11-2023 03:19
From: Dan Burleson
Subject: PR Employee Documents - Email Address

That field (PR_EmployeeDocuments.EmailAddress) is loaded unencrypted into the PR_EmployeeListingWrk table. One can link that table by EmployeeKey and filtering  records where Document="Check" and "TaxGroup" = "". I have a script solution that I sell very reasonably that will automate the access to an up-to-date version of this table inside or outside of Sage. You can test if it will work for you by putting the Employee Listing in preview using the setting for "Report Type to Print" as "Employee Information" and checking the option for "Paperless Office Delivery". If you're dealing with Adv/Std, note the session number (that's the numeric digits in the form USRMMDDYYnnnnnn at the end of the work file e.g. PR_EmployeeListingWrkUSRMMDDYYnnnnnn.M4T) and putting that into the "Session ID" field of the ODBC control panel of SOTAMAS90. If its Premium you can check out this thread link.  If this works for you contact me if you would like it automated.

------------------------------
Dan Burleson
Software Consultant
Connex Software
------------------------------


Original Message:
Sent: 01-10-2023 17:04
From: Chris Love
Subject: PR Employee Documents - Email Address

That gives me PR_Employee.EmailAddress, but I need PR_EmployeeDocuments.EmailAddress

------------------------------
Chris Love
Accounting Systems, Inc. (ASI)


Original Message:
Sent: 01-10-2023 16:55
From: Jim Woodhead
Subject: PR Employee Documents - Email Address

if you add the report as a custom report on the reports menu you can have the PII field added., just join it via the employee key..  When  you add it to the menu via the  custom wizard it will get you the option to add the PR_Employee_PII table and all the data will be available,

------------------------------
Jim Woodhead
DSD Business Systems
619-990-3946

Dan's answer got me where I needed to go.  All I needed was the PR_EmployeeListingWrk table.  Filter records where ReportSection = "B".  This will give you both Check and Direct Deposit Stub, so you can filter further on those if you want. I chose not to further filter. 

But this was very helpful. I had no idea that Paperless info was in this work table, so that saves a lot of headache moving forward.

------------------------------
Chris Love
Accounting Systems, Inc. (ASI)
------------------------------

Original Message:
Sent: 01-11-2023 03:19
From: Dan Burleson
Subject: PR Employee Documents - Email Address

That field (PR_EmployeeDocuments.EmailAddress) is loaded unencrypted into the PR_EmployeeListingWrk table. One can link that table by EmployeeKey and filtering  records where Document="Check" and "TaxGroup" = "". I have a script solution that I sell very reasonably that will automate the access to an up-to-date version of this table inside or outside of Sage. You can test if it will work for you by putting the Employee Listing in preview using the setting for "Report Type to Print" as "Employee Information" and checking the option for "Paperless Office Delivery". If you're dealing with Adv/Std, note the session number (that's the numeric digits in the form USRMMDDYYnnnnnn at the end of the work file e.g. PR_EmployeeListingWrkUSRMMDDYYnnnnnn.M4T) and putting that into the "Session ID" field of the ODBC control panel of SOTAMAS90. If its Premium you can check out this thread link.  If this works for you contact me if you would like it automated.

------------------------------
Dan Burleson
Software Consultant
Connex Software
------------------------------


Original Message:
Sent: 01-10-2023 17:04
From: Chris Love
Subject: PR Employee Documents - Email Address

That gives me PR_Employee.EmailAddress, but I need PR_EmployeeDocuments.EmailAddress

------------------------------
Chris Love
Accounting Systems, Inc. (ASI)


Original Message:
Sent: 01-10-2023 16:55
From: Jim Woodhead
Subject: PR Employee Documents - Email Address

if you add the report as a custom report on the reports menu you can have the PII field added., just join it via the employee key..  When  you add it to the menu via the  custom wizard it will get you the option to add the PR_Employee_PII table and all the data will be available,

------------------------------
Jim Woodhead
DSD Business Systems
619-990-3946

Original Message:
Sent: 1/11/2023 12:12:00 PM
From: Chris Love
Subject: RE: PR Employee Documents - Email Address

Dan's answer got me where I needed to go.  All I needed was the PR_EmployeeListingWrk table.  Filter records where ReportSection = "B".  This will give you both Check and Direct Deposit Stub, so you can filter further on those if you want. I chose not to further filter. 

But this was very helpful. I had no idea that Paperless info was in this work table, so that saves a lot of headache moving forward.

------------------------------
Chris Love
Accounting Systems, Inc. (ASI)
------------------------------


Original Message:
Sent: 01-11-2023 03:19
From: Dan Burleson
Subject: PR Employee Documents - Email Address

That field (PR_EmployeeDocuments.EmailAddress) is loaded unencrypted into the PR_EmployeeListingWrk table. One can link that table by EmployeeKey and filtering  records where Document="Check" and "TaxGroup" = "". I have a script solution that I sell very reasonably that will automate the access to an up-to-date version of this table inside or outside of Sage. You can test if it will work for you by putting the Employee Listing in preview using the setting for "Report Type to Print" as "Employee Information" and checking the option for "Paperless Office Delivery". If you're dealing with Adv/Std, note the session number (that's the numeric digits in the form USRMMDDYYnnnnnn at the end of the work file e.g. PR_EmployeeListingWrkUSRMMDDYYnnnnnn.M4T) and putting that into the "Session ID" field of the ODBC control panel of SOTAMAS90. If its Premium you can check out this thread link.  If this works for you contact me if you would like it automated.

------------------------------
Dan Burleson
Software Consultant
Connex Software
------------------------------


Original Message:
Sent: 01-10-2023 17:04
From: Chris Love
Subject: PR Employee Documents - Email Address

That gives me PR_Employee.EmailAddress, but I need PR_EmployeeDocuments.EmailAddress

------------------------------
Chris Love
Accounting Systems, Inc. (ASI)