web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics SL (Archived) / AP Document Maintenanc...
Microsoft Dynamics SL (Archived)

AP Document Maintenance Customization

(0) ShareShare
ReportReport
Posted on by joe 935

I would like to customize the AP Document Maintenance Screen (03.250.00).  I want to add a check number field to that screen so when the user looks up a refernce number they will see which check number was used to pay. 

Here is my issue...

I've combed through SQL and it appears the only table that has both the source refnbr and the check number is the APAdjust table (AdjdRefNbr = Source Ref number and AdjgRefNbr = the check number).

My problem is when I customize the AP Document Maintenance screen and add an object (textbox) the APAdjust table is not available.  My question is can I add the APAdjust table to my list of options when adding a textbox?

 Please let me know if anyone can help with this.  Thank you!

*This post is locked for comments

I have the same question (0)
  • Gail J-N Profile Picture
    Gail J-N 1,000 on at

    That will be tricky since the possibility exists that more than one check was used to pay the document. 

                   APDoc <-->>APAdjust

    In other words, APDoc has a one-to-many relationship with APAdjust. 

    It might be simpler to run a nightly (more frequent?) query via SQL Agent to populate the minimum check number from APAdjust into an APDoc.User[X] field, and then display the APDoc.user[X] field on the 03.250 AP Document Maintenance screen (display only of course!).

    Code to look at the relationship of the APDoc record to the APAdjust record might look like this:

    select apdoc.cpnyid, apdoc.perpost,apadjust.perappl, apdoc.vendid,  apdoc.doctype,
    apdoc.refnbr as VoucherNbr, apadjust.adjgrefnbr as ChkNbr,
    apdoc.origdocamt as DocAmt, apadjust.adjamt as ChkAmt, apadjust.AdjDiscAmt as TermsDiscount
    from apdoc (nolock)
    inner join apadjust (nolock) on apdoc.refnbr = apadjust.AdjdRefNbr
     and apdoc.doctype = apadjust.adjddoctype and adjgdoctype in ('CK','HC','ZC')
     --be sure to eliminate vouchers cleared via debit adjustments
    where apdoc.perpost in (select pernbr from apsetup) 
     --Only select current period so query run faster

    Code to look at the minimum check number associated with an APDoc voucher would look like this:

     select apdoc.cpnyid, apdoc.doctype, apdoc.refnbr as VoucherNbr, min(apadjust.adjgrefnbr) as ChkNbr
    from apdoc (nolock)
    inner join apadjust (nolock) on apdoc.refnbr = apadjust.AdjdRefNbr
       and apdoc.doctype = apadjust.adjddoctype and adjgdoctype in ('CK','HC','ZC')
        --be sure to eliminate vouchers cleared via debit adjustments
    where apdoc.perpost in (select pernbr from apsetup)
    group by apdoc.cpnyid, apdoc.doctype, apdoc.refnbr
     
    Hope this helps.

    Gail J-N

  • joe Profile Picture
    joe 935 on at
    Wow! thank you for the response. I like your solution we'll give that a shot. Thanks!
  • Gail J-N Profile Picture
    Gail J-N 1,000 on at

    I mentioned your idea to our AP person, who said heck yes, she wants a check number on her AP document maintenance screen (03.250).

    So for those lurkers who might see this, here's how we did our customization.

    I checked to make sure that APDoc.User6 & APDoc.USer7 were not in use already.  Then I added APDOc.User6 onto the Document maintenace screen as "Paid by Check Number" and APDoc.User7 as "Check Date".   These were both added as "Display Only" fields (Enabled = False).   I actually put these up on the "Manual Check" tab since we never use that, and relabeled it as "Check Info".

    Using a form of the query below, I updated all of our vouchers since 2007-01.   To update on an on-going basis, I added the query below to a nightly SQL Agent job to ensure that daily check runs update the database at night.   [Sorry I don't do triggers.]

    Note that in the case where more than one check clears a voucher, the code populates an asterisk ("*") into the APDoc.User6 (check number) field.

    WARNING:   The code below updates data and should be tested in your environment before use.   Use at your own risk.

    Gail J-N

    --Warning Use at your own risk
    --Always TEST any code to ensure that it works correctly in your environment
    --This code assumes that the user fields APDoc.User6 and APDoc.User7 are unused and available
    --APDoc.User6 will be populated with a check number if only one check clears the voucher
    --APDoc.User6 will be populated with an asterisk ("*") if more than one check clears the voucher
    --APDoc.User7 will be populated with the docdate of the check number

    --first find any that have more than one check and mark those with "*"
    --must be in accounting period >= pernbr in GLSetup

    update apdoc
    set apdoc.user6 = '*'
    --select apdoc.cpnyid, apdoc.doctype, apdoc.refnbr, s1.chknbr
    from apdoc
    inner join (select apdoc.cpnyid, apdoc.doctype, apdoc.refnbr as VoucherNbr, min(apadjust.adjgrefnbr) as ChkNbr
                    from apdoc (nolock)
                   inner join apadjust (nolock) on apdoc.refnbr = apadjust.AdjdRefNbr
                        and apdoc.doctype = apadjust.adjddoctype and adjgdoctype in ('CK','HC','ZC')
                    where apdoc.perpost >= (select pernbr from glsetup) and apdoc.docbal = 0
                    group by apdoc.cpnyid, apdoc.doctype, apdoc.refnbr having count(*) > 1) S1
                          on apdoc.cpnyid = s1.cpnyid and apdoc.doctype = s1.doctype and apdoc.refnbr = s1.VoucherNbr
    where apdoc.user6 = ''

     

    --next find & update the majority that are paid with a single check

    update apdoc
    set apdoc.user6 = s1.ChkNbr
    --select apdoc.cpnyid, apdoc.doctype, apdoc.refnbr, s1.chknbr
    from apdoc
    inner join (select apdoc.cpnyid, apdoc.doctype, apdoc.refnbr as VoucherNbr, min(apadjust.adjgrefnbr) as ChkNbr
                   from apdoc (nolock)
                   inner join apadjust (nolock) on apdoc.refnbr = apadjust.AdjdRefNbr
                            and apdoc.doctype = apadjust.adjddoctype and adjgdoctype in ('CK','HC','ZC')
                   where apdoc.perpost >= (select pernbr from glsetup) and apdoc.docbal = 0
                       group by apdoc.cpnyid, apdoc.doctype, apdoc.refnbr having count(*) = 1) S1
                                on apdoc.cpnyid = s1.cpnyid and apdoc.doctype = s1.doctype and apdoc.refnbr = s1.VoucherNbr
    where apdoc.user6 = ''

    --finally link back to the original check and get the check date
    update apdoc
    set apdoc.user7 = apdocchk.docdate
    --select apdoc.cpnyid, apdoc.doctype, apdoc.refnbr, apdoc.user6 as ChkNbr, apdocchk.docdate as CheckDate, apdoc.origdocamt, apdoc.DiscTkn, apdocchk.origdocamt as ChkAmt
    from apdoc
    inner join apdoc apdocchk on apdoc.cpnyid = apdocchk.cpnyid
                          and apdocchk.doctype <> ('VO') and apdoc.user6 = apdocchk.refnbr
    where apdoc.perpost >= (select pernbr from glsetup)
    and apdoc.user6 <> '' and apdoc.user7 = '01/01/1900'

  • joe Profile Picture
    joe 935 on at
    That's great! I like your enhancements! We went ahead and created a job in the agent to insert the lowest check number into the User5 field then added it to the Document Maintenance screen. Just when I thought I was done, now I may have to incorporate the asterisk and the date! ;-) Thanks again for the help!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics SL (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans