Access – Undefined function when you open a form

The problem was the location at which access DB as saved was not defined as trusted location.  Once the location was set as trusted location, the issue was resolved.

How to set trusted location in access 2007:

1) Open Access
2) Click on the Office Button
3) Click on the Access Options
4) Click on Trust Center
5) Click on Trust Center Settings
6) Click on Trusted Location
7) Add the Access DB Location to be trusted
8) Click ok

Cheers!
Sanjay

 

 

 

 

Dynamics GP – Purchase Order Line status export and reconciliation – Script

Today received a request to provide an export of all purchase order line items which were open with data enabling the user to sum the outstanding amounts for each PO line item to match with the purchase orders remaining sub total amount.

Tables used:
POP10500 – Purchasing Receipt Line Quantities
POP10110 – Purchase Order Line
POP10100 – Purchase Order Work
GL00105 – Account Index Master
IV00101 – Item Master

The below script extracts the required data:

--drop table ##polines 
--drop table ##polines2 
SELECT dbo.pop10500.ponumber, 
       dbo.pop10500.vendorid, 
       dbo.pop10500.itemnmbr, 
       dbo.pop10500.uofm, 
       dbo.pop10500.curncyid, 
       dbo.pop10500.polnenum, 
       Sum(dbo.pop10500.qtyshppd)      AS Shipped, 
       Sum(dbo.pop10500.qtyinvcd)      AS Invoiced, 
       Sum(dbo.pop10500.qtyrej)        AS Rejected, 
       Sum(dbo.pop10500.qtymatch)      AS Matched, 
       Sum(CASE dbo.pop10500.poptype 
             WHEN 5 THEN dbo.pop10500.qtyreserved 
             ELSE 0 
           END)                        AS QTYReserved, 
       Sum(dbo.pop10500.qtyinvreserve) AS qtyinvreserve 
INTO   ##polines 
FROM   dbo.pop10500 
GROUP  BY ponumber, 
          vendorid, 
          itemnmbr, 
          uofm, 
          curncyid, 
          polnenum 

SELECT dbo.pop10110.itemnmbr, 
       dbo.pop10110.ponumber, 
       dbo.pop10110.vendorid, 
       CASE dbo.pop10100.potype 
         WHEN 1 THEN 'Standard' 
         WHEN 2 THEN 'Drop Ship' 
         WHEN 3 THEN 'Blanket' 
         WHEN 4 THEN 'Blanket Drop ship' 
       END 
       AS POTYPE, 
       dbo.pop10100.docdate, 
       Year(dbo.pop10100.docdate) 
       AS year, 
       CASE dbo.pop10100.postatus 
         WHEN 1 THEN 'New' 
         WHEN 2 THEN 'Released' 
         WHEN 3 THEN 'Change order' 
         WHEN 4 THEN 'Received' 
         WHEN 5 THEN 'Closed' 
         WHEN 6 THEN 'Cancelled' 
       END 
       AS [PO Status], 
       dbo.pop10110.unitcost, 
       ( dbo.pop10110.qtyorder - dbo.pop10110.qtycance - Isnull( 
         ##polines.shipped, 0) ) 
       * dbo.pop10110.unitcost 
       AS EXTDCOST, 
       dbo.pop10110.noninven, 
       dbo.pop10110.ord, 
       dbo.pop10110.reqdate, 
       dbo.pop10110.prmdate, 
       dbo.pop10110.prmshpdte, 
       dbo.pop10110.reqstdby, 
       dbo.pop10110.qtyorder 
       AS Ordered, 
       dbo.pop10110.qtycance 
       AS Cancelled, 
       Isnull(##polines.shipped, 0) 
       AS Shipped, 
       Isnull(##polines.invoiced, 0) 
       AS Invoiced, 
       Isnull(##polines.rejected, 0) 
       AS Rejected, 
       Isnull(##polines.matched, 0) 
       AS Matched, 
       dbo.pop10110.qtyorder - dbo.pop10110.qtycance - 
       Isnull(##polines.invoiced, 0) AS 
       RemainingQty, 
       ( dbo.pop10110.qtyorder - dbo.pop10110.qtycance - Isnull( 
         ##polines.invoiced, 0) 
       ) * dbo.pop10110.unitcost 
       AS RemainingExtd, 
       Isnull(##polines.qtyinvreserve, 0) 
       AS qtyinvreserve, 
       Isnull(##polines.qtyinvreserve, 0) * dbo.pop10110.unitcost 
       AS extinvreserve, 
       Isnull(##polines.qtyreserved, 0) 
       AS QTYReserved, 
       Isnull(##polines.qtyreserved, 0) * dbo.pop10110.unitcost 
       AS extQTYReserved, 
       dbo.gl00105.actnumst, 
       dbo.pop10100.remsubto, 
       dbo.pop10110.polnesta 
INTO   ##polines2 
FROM   dbo.gl00105 WITH (nolock) 
       RIGHT OUTER JOIN dbo.pop10100 WITH (nolock) 
                        INNER JOIN dbo.pop10110 WITH (nolock) 
                                ON dbo.pop10100.ponumber = dbo.pop10110.ponumber 
                        LEFT OUTER JOIN ##polines 
                                     ON dbo.pop10110.ponumber = 
                                        ##polines.ponumber 
                                        AND dbo.pop10110.ord = 
                                            ##polines.polnenum 
                     ON dbo.gl00105.actindx = dbo.pop10110.invindx 
WHERE  ( dbo.pop10110.linenumber <> 0 ) 
       AND ( dbo.pop10100.postatus IN ( 1, 2, 3 ) ) 
GROUP  BY dbo.pop10110.itemnmbr, 
          dbo.pop10110.qtyorder, 
          dbo.pop10110.qtycance, 
          dbo.pop10110.unitcost, 
          dbo.pop10110.extdcost, 
          dbo.pop10110.noninven, 
          dbo.pop10110.reqdate, 
          dbo.pop10110.prmdate, 
          dbo.pop10110.prmshpdte, 
          dbo.pop10110.reqstdby, 
          dbo.pop10100.postatus, 
          dbo.gl00105.actnumst, 
          dbo.pop10100.potype, 
          dbo.pop10100.docdate, 
          dbo.pop10110.ponumber, 
          dbo.pop10110.vendorid, 
          dbo.pop10110.itemnmbr, 
          dbo.pop10110.uofm, 
          dbo.pop10110.curncyid, 
          dbo.pop10110.ord, 
          ##polines.shipped, 
          ##polines.qtyreserved, 
          ##polines.qtyinvreserve, 
          ##polines.invoiced, 
          ##polines.rejected, 
          ##polines.matched, 
          ##polines.qtyreserved, 
          dbo.pop10100.remsubto, 
          dbo.pop10110.polnesta 

SELECT ##polines2.*, 
       Isnull(dbo.iv00101.itemdesc, '') AS [Item Description] 
FROM   ##polines2 
       LEFT OUTER JOIN dbo.iv00101 
                    ON ##polines2.itemnmbr = dbo.iv00101.itemnmbr 
--drop table ##polines 
--drop table ##polines2

The challenge was to demonstrate the sum of the PO line items outstanding were reconciled to the remaining subtotal of Purchase Order according to GP.  The reconciliation can be done by creating the pivot table with extracted data as follows:

PO Lines Report

PO Lines Report

 

Cheers!
Sanjay

FRX Report Date is not setting.

frx report date not setting

frx report date not setting

In this situation the FRX dates are not sticking to newer periods.  The fiscal periods at Dynamics GP has been created, the old period has been closed.  The problem was associated to only one user. The sticky situation was resolved as follows:

1) The sysdata directory was backed up.

2) All users using FRX were requested to log off.

3) All G32 files at the SYSDATA folder was deleted.

4) Logged as user who did not have the did not have the sticky date issue.

5) Created indexes.

The issue was resolved of all users.

Cheers!
Sanjay

 

FRX – Cannot find GL Index database – [path].G32

Frx index not found

Frx index not found

Though FRX is retired, it is still used in production. There are situations where index files (*.G32 at sysdata folder) needs to be deleted, for example the date is not setting properly when running the report. Once the index files are deleted, it needs to be re-created.

Indexes for the company can be created by making the logged in company the default company @ Company -> Information -> Set as default.

However the indexes for the forecaster company is not created when the logged in company is set at default. In order to create the indexes for the forecaster company, first identify the forecaster company from the Company -> Information screen of the logged in company. Next select the forecaster company and make it default. Indexes for the forecaster company is created.

Select the company to be logged in make it the default company to revert back to the reporting company.

At this point indexes for the company and the forecaster company is generated and all set for reporting.

PS: Always backup the sysdata folder before any operation deletion or rebuild operation in FRX.

Cheers!
Sanjay

Dynamics GP – Parent – Child Apply Script

Often requests are received to compile a list of payments from parent and details of how the payment is applied.  The below script extracts the parent payment  and child apply to information.

DECLARE @CUSTNUMBR AS VARCHAR(21) 
DECLARE @FROMDTE AS DATETIME 
DECLARE @TODTE AS DATETIME 

SELECT @CUSTNUMBR = 'CUSTOMERNUMBER' 
SELECT @FROMDTE = CONVERT(DATETIME, 'YYYY-MM-DD 00:00:00', 102) 
SELECT @TODTE = CONVERT(DATETIME, 'YYYY-MM-DD 00:00:00', 102) 

SELECT custnmbr, 
       cprcstnm, 
       trxsorce, 
       glpostdt, 
       posted, 
       aptodcnm, 
       aptodcdt, 
       aptodcty, 
       curncyid, 
       apptoamt, 
       apfrdcnm, 
       apfrdcty, 
       fromcurr 
FROM   rm20201 
WHERE  apfrdcnm IN (SELECT docnumbr 
                    FROM   rm20101 
                    WHERE  ( custnmbr = @CUSTNUMBR ) 
                           AND ( glpostdt >= @FROMDTE ) 
                           AND ( glpostdt <= @todate ))

 

Cheers!
Sanjay