Dynamics GP – Can’t Invoice Match PO

Devon SouthallLeave a comment

This is part two… please refer to part 1 if you haven’t already followed those steps…

For invoice match we have a few more tables to look at in SQL

  • POP10600 – Purchasing Shipment Invoice Apply — this is the table the ties the PO receipt record to the PO invoice match record (seen in the blue arrow expansion window Match Shipments to Invoices)
  • POP30300 & POP30310 Purchasing Receipt Header and Lines History — we need to know about posted invoice matches, POPTYPE=2 (1 is shipment/receiving)

Also, during invoice match entry, in POP10500, the receiving line (QTYSHPPD>0 ) gets changed — QTYMATCH gets populated. (This entry is actually what “holds” the quantity for the Quantity status window — so this is super important for troubleshooting! (*** see sidenote at the end for more on this))

Remember my benchmark in SQL for troubleshooting is “what should a GOOD record look like?” So with that in mind – here is an example of a GOOD unposted invoice match transaction

In Purchasing Invoice entry window:

here are some important fields in POP10500 for that same PO number:

Let’s walk thru this:

  • The first row is our Shipment/receiving line – RCT1128 (Listed under Matched to Shipment on the middle grid on the entry window)
    • We can tell this is our receiving line because QtyShppd (Qty Shipped) is greater than zero
    • note the Status is 1 – posted
    • Also see that the Qty Match is not zero – but it the 2 that was matched. (This column was zero before this invoice match transaction was entered)
  • The second row is our Invoice match line — RCT1165 (the transaction number on the top left in the transaction entry screenclip)
    • note the States is 0 – unposted
    • Also see the QtyShppd (Qty Shipped) is zero but QtyInvcd (Qty Invoiced) is 2 — this tells us this is an Invoice Match transaction

Here is the record in POP10600 – you can see how it ties these two lines together:

OK. lets go thru common situations for “Can’t Invoice Match”:

  • You find a receiving record in POP1050 with QTYMATCH that doesn’t have a corresponding record in POP10600.
    • This is the most common situation. My hypothesis is that someone started to enter an invoice match transaction then backed out or deleted it and the transaction got properly removed from the other tables but the Shipping line QtyMatch was NOT properly re-set.
    • QtyMatch on POP10500 should equal the total for all records in POP10600 QtyInvcd for that same PORCTNM and RCPTLNNM
    • the QTY Match on POP1050 “holds” the quantity, so correcting this field will release the quantities in GP.
  • The other situations are that you also find parts of an invoice match record in POP10310, POP10500, etc.
    • You find a record in POP1050 for that PONUMBER Status=0 (unposted), QtyShppd=0, QtyInvcd >0 (invoice match record).
      • You can NOT pull up and delete the POPRCTNM in Purchasing invoice entry window
      • You may or may not find records in POP10310 and POP10300 (doesn’t matter either way)
      • You do NOT find the POPRCTNM in POP30300 or POP30310 (history tables header & line)
      • Follow all of the steps above to Fix the receiving line’s QtyMatch – this will release the quantities
      • delete these record(s). delete any related rows in the header table POP10300, POP10600 match table and optional tables from the other post (POP10330, POP10360, POP10390, POP10306, POP10340), if any. This will clean up the work tables so that there isn’t bad records for later problems.
    • If you DO find the POPRCTNM transaction in POP30300 and POP30310 then it just didn’t finish ALL the steps for posting. Check if it posted to inventory (if applicable), GL and payables. If it did, you will want to remove any records in the work tables POP103XX (make sure ALL lines are in POP30310), then verify work/history tables – POP10500 and POP10600 — have status =1 / posted.

Hope this is helpful and happy troubleshooting!

** sidenote — this QTYMATCH getting populated when Invoice Match transactions are ENTERED even before they are POSTED, has a super important side effect in GP. The GP Received Not Invoice report (subledger) is effected by these transactions in WORK (EG the related receiving transactions are not listed on the report), effectively rendering the report not accurate when Invoice match transactions are pending / unposted. So, best practice is to make sure there are no pending Invoice Match transactions before running the report at month end. <Sigh>

Dynamics GP – HELP! I can’t receive or invoice match this PO

Devon SouthallLeave a comment

A very common troubleshooting issue in Dynamics GP is that a user can’t receive or can’t invoice match a Purchase Order. This post will detail out some steps for troubleshooting both situations – with follow-up post for additional troubleshooting for the “Can’t Invoice match” separately. (This is a full write-up of one of the tips I shared at my Community Summit 2024 presentation “Fueling Efficiency: 10 tips to Powering Up GP Purchase Orders with SQL & Utilities”)

Your first step is to open your GP Purchase order in PO Entry or PO inquiry. Click on one of the lines that can’t be processed and click the “i” button on the line header (below in red circle) to view the Purchasing Quantity Status window.

On the bottom right is the quantities that SHOULD be able to be received (Remaining to Ship) and invoice matched (Remaining Posted Shipments to Match).

Pay close attention to anything in the middle right Unposted column. If there is some quantity there that users are unaware of, that can be the issue.

If you can’t find the unposted transaction the normal way (Inquiry >Purchase Order Documents – enter PO and don’t forget to check the boxes in the middle before pressing Redisplay!), go the respective transaction entry window (receiving, invoice match or, for that matter, return) and press the scroll arrows on the bottom left to look for “bad” transactions. If one comes up, many times, you can successfully delete this transaction in the GP window; releasing the quantity. (Use scroll arrows to confirm it was deleted.)

If you STILL can’t find it, or it won’t delete, then you will need to head into SQL to clear the transaction (or call your partner/IT department to do that for you!). Best practice is to ONLY fix in SQL AFTER you try the transaction entry windows!!!

My benchmark in SQL is “what should a GOOD record look like?” So with that in mind – tables to look at:

  • POP10310 Purchasing Receipt Lines Work
    • look for field PONUMBER equals your PO number
    • if you find a record here, check the header table also – POP10300 Purchasing Receipt Work – but generally transactions with header records are visible in the GP windows in our first step.
    • if you find records in the line table- ALSO look at the following OPTIONAL tables for the matching POPRCTNM
      • POP10330 Purchasing Serial Lot Work
      • POP10360 Purchasing Tax
      • POP10390 Purchasing Distribution Work
      • POP10306 Purchasing Receipt User-Defined
      • POP10340 Purchasing Bin Quantities Work
  • POP10500 Purchasing Receipt Line Quantities table
    • look for field PONUMBER equals your PO number
    • this table contains BOTH WORK (unposted) & HISTORY (posted transactions)
      • look at the field Status
        • 0 = unposted
        • 1 = posted
    • Also contains rows for BOTH receiving and invoice matches
      • receiving lines have QTYSHPPD>0 (this would be ship/invoice lines too.)
      • invoice match lines have QTYSHPPD=0

OK. lets go thru common situations for “Can’t Receive”:

  • you find a record in POP10310 and record in POP1050 both with the same transaction number POPRCTNM and line number RCPTLNNM. the Status in POP10500 is 0 (unposted).
    • delete these record(s). delete any related rows in the header table POP10300 and optional tables above, if any. This should release the quantities.
  • you only find a record in POP1050 Status is 0 (unposted). This POPRCTNM is NOT in POP10310
    • check if the POPRCTNM is in POP30310 Purchasing Receipt Lines History. if you do not find it, then you can delete this record, this is an abandoned/orphan record. Deleting this record should release the quantities.
  • Note – records in POP10310 without records in POP10500 won’t cause these issues, because the POP10500 table “holds” the PO quantities!

Typically, the advice above will release any stuck situations for “Can’t receive” problems. If you are working on a “Can’t Invoice match” problem follow on for more advice… >link<

Hope this is helpful and happy troubleshooting!

Dynamics GP Inventory – Item History Window

Devon SouthallLeave a comment

Let’s talk about the Dynamics GP Inventory Item History Window. This is a “hidden” window that not many folks know about. It is accessed not from a menu, but only from the GoTo on Item Maintenance or Item Inquiry. *Edit – David Musgrave pinged me after posting that he used the GP Power Tools and this window is actually on a menu but not in an expected spot – it is under Inventory > CARDS! – see note at the end for more discussion on this.

There are many posts on where the data comes from – but sadly most are not accurate.
I had a client asking questions about this window – so I figured I would post where the data really comes from. First, all of this is controlled by the “keep history” checkboxes on EACH item maintenance card (options button bottom right – Keep History: Calendar or Fiscal). No history checkboxes, no records in this window.

The top section with Current year and Last Year is SALES numbers from the SQL table IV30101 (Inventory Sales Summary History). During the inventory year end close routine, the current year numbers are moved to Last year and then cleared out.  Since this is NOT date sensitive, but based upon when the routine is run – these numbers might not be accurate.

The radio button in the middle left “Year: Calendar/Fiscal” changes the display in the bottom grid.
Further to the right you can select a specific site filter for this grid, or leave it to display the record for all sites.

Annual Historical Usage uses your GP system date to total up the prior period history records for BOTH sales and demand. Help says it “Displays the annual usage quantity for the selected item, calculated from sales and dependent demand history for the last year, starting with the month or period prior to the current month or period”  The Projected Usage is interesting, but I have never seen anyone use it – Check out the help > about this window for more information. 

The Lower grid contains summary data updated by transaction posting from SQL table IV30102 (Inventory Sales Summary Period History).

The first row displays sales totals, additionally the second row shows item usage in Inventory assemblies or Manufacturing Orders as “Dependent Quantity”  (press the down chevrons on the far right of the title bar to see the second row.)

I have had clients use this data in SmartLists and/or other analysis tools besides using it in this screen.

Hopefully this is helpful.

Best, Devon

** Edit – post discussion — I find that GP programmers put this window under Cards and not Inquiry fascinating, probably related to the “Projected usage” feature that I mentioned above. But it brings up an VERY important point that I missed in the original post — the fields are able to be changed (editable) by users.  The top annual fields are editable all the time and the lower grid fields are editable when a specific site is selected. 

Building Form GoTo’s for Receivables & Customers

Devon Southall1 Comment

Here are Steps for building the most common GoTo’s for the Receivables module:

Basic Steps (Unless listed differently below):

  1. Press the Go To … button on the middle of the top bar in SmartList Builder (Or Open Drilldown Builder.)
  2. Enter your Description
  3. Product is Microsoft Dynamics GP
  4. Series is Sales
  5. Select Form listed below
  6. Press + on the middle right of the blue bar to add a task
  7. Add task(s) as below, press Save on Add Task window
  8. Press Save on Add Go To – Open Form window

Specific Windows (Forms):

  • Customer Maintenance – “Set a field value and run the field script” on field Customer Number
  • Address Maintenance

    .
  • Receivables Transactions Inquiry Zoom — this is accessible via an Advance Go Tos rather than a Form Go To – use the Receivable Transaction Inquiry (Also will take you to a Cash Receipt Inquiry Zoom for a payment.):   **See note below

 

  • Customer Payment Summary Inquiry  – “Set a field value and run the field script” on field Customer Number
  • Customer Yearly Summary Inquiry  – “Set a field value and run the field script” on field Customer Number  Will open by default to year of your GP date
     
  • Customer Period Summary Inquiry  – “Set a field value and run the field script” on field Customer Number  Will open by default to current period (based upon GP date)
     
  • Customer Inquiry – this uses a calculated field called Integer3 (Type integer, in the calculation area enter the number 3) to select the “Bill to” address (Third value in the dropdown)

  • Customer Transaction Inquiry – this Go To loads the customer, but also sorts by date, unchecks to display History (at least for Active customers) and Shows Details (second line for each transaction) See example screen below:


    .
  • Collections Management Main — Note that the Product and Series is different than the basic steps:

    .
  • Transaction entry (For transactions in Work status)  **See note below
  • Cash Receipts Entry (For transactions in Work status)  **See note below

    .
  • Apply Sales Documents (For Open Transactions, Payment, Return or Credit Memo types only)

    .
  • Edit Transaction (Open only)  (The dropdown for Type doesn’t have #2 Scheduled Payments.  A Calculated field is needed.)
  • Posted Transaction (Open only)  (The dropdown for Type doesn’t have #2 Scheduled Payments.  A Calculated field is needed. See above.)

    .
  • Receivable Batches this uses a calculated field called Integer2 (Type integer, in the calculation area enter the number 2) to select the “Cash receipts entry” (Second value in the dropdown) – Use 1 for Receivable Transaction batches

** Unlike the SmartLists that come from GP – we are not able to build a Go To that will open the Entry window for transactions in Work status and the Inquiry window for transactions in Open or History status.

Hopefully this helps!   Let me know if I have missed a Receivable window.  Next post, Payables!

Happy Building,
Devon

Adding Tasks for a Open Form Go To in the Builders

Devon Southall1 Comment

If you want your Go To in SmartList Builder or your Drilldown Builder to open the form AND load related data, you will need to add Tasks to your Go To.  Your Go to will need to reproduce the same steps, in the same order, that users do to pull up data on that form. 

Your Go To needs to enter data, leave fields, check boxes, select radio button options and pick options from dropdown fields just like a user would.  Each of these tasks has its own tricks:

  • Fields that you enter data into:
    • Trickiest part is figuring out the name of the field that you enter the data into.  They will appear on the Field dropdown in the order that you tab thru in on the window.  But the name may not be what you expect.  You may need to try a couple fields or if you have access to Dexterity, Modifier or the Support Debugging tool you can find the exact name of the field on the window. 

    • Use the “Set value of a field and run the field script” task type
  • Dropdown fields:
    • Each option on the list is represented with an integer (1,2,3, etc). Typically they are numbered in the order they appear in the dropdown, but you may not be seeing all the options. 
    • Note – Some fields may have a Dexterity feature selected to display the options in alphabetical order – which means that you will have to use another method to determine what integer represent each value. (There is always trial and error!)
    • Create a calculated field to hold your desired integer value to pass to the Go To.
    • Use the “Set value of a field and run the field script” task type
  • Radio button options – these are the little circles that you can only pick one of the several circles:
    • Each circle is numbered in the order that you tab thru them, starting with zero.
    • You need to find the name for the Radio Group field and pass the correct number for your option to that field
    • Again create a calculated field to hold your desired integer value to pass to the Go To.
    • Use the “Set value of a field and run the field script” task type
  • Selecting or un-selecting Checkboxes – These are a bit problematic. 
    • Better minds than I have been stumped by this in SmartList Builder.  One solution is to use VBA to check a Checkbox to make your GoTo’s work (Mariano)  In this discussion, macros were discarded as a solution because it will toggle the field, first time it will uncheck, but the next time it will check.   I have tried using every type of calculated field and SQL table field, etc with no luck, except:
    • I have found is that you can use a GP Yes/No field to trigger a checkbox! … now since Yes/No is, unfortunately, NOT a calculated field type, you must have a Yes/No field on your GP tables that always stores the correct value.   Try “Voided” or “Inactive” for No and, maybe, various “Keep History” fields for Yes.  It is a little odd, but try it – it might just work for you!    (And sorry – a SQL yes/no or bit field won’t work.)
    • Use the “Set value of a field and run the field script” task type
  • Show/Hide Details
    • Use the “Run field script” task type
    • Field will be named something like:
      • “Scrolling Window Expand button” or  “Scrolling Window Shrink Button”  or sometimes just  “Scrolling Window Shrink”
      • “Show Summary Button” or “Show Detail Button”
      • “(L) Expand Button” or “(L) Shrink Button”
      • Typically will NOT be the field with Switch in the name – this is just an image field  (unless there is no Button field – then the programmer may have attached the field script to the image field.  A field “(L) ShrinkExpandSwitch” with scripting is part of the Dexterity samples)
  • Buttons
    •  Use the “Run field script” task type
    • Button names typically have the words on the button and “Button” in the field name like “Save Button”  or “Hold Button” or “Redisplay Button”
    • Blue Arrows are buttons called “Expansion Button” with a number.  You may need to use Trial and Error to determine the correct one to trigger (unless you have access to Modifier or Dexterity to see the exact field name).

As always – remember to build your Go To step by step and to test after each step – if you make a mistake nothing will happen when you select your Go To.

Hopefully, this list will help you add Go To’s for many parts of GP.  Let me know if you discover any more Tricks and I will update this post.  In the next posts of the series I will be using these steps to build sample Go To’s for many of the common GP screens.  

Until then – Happy Building!
Devon

Tricks for building an Open Form GoTo in the Builders

Devon Southall1 Comment

GoTo’s are one of the best features of SmartList Builder, Navigation List Builder and now, via the Drilldown Builder, Excel reports and SSRS reports.  Here are some points to remember when building your Go To:

  • You can only open forms that have fields that users can enter the key data into.   Sometimes this isn’t obvious.   At first look the Item Purchasing Options would seem like it wouldn’t work – the key field Item Number is greyed out.
     .
    But if you hit the Save button the item number field becomes accessible.

    In contrast, there are no buttons on the Manufacture’s Item Number Maintenance that will make the Item Number accessible.  You can’t build a GoTo to open this form.

    •  

NOTE – some of the most popular forms that can’t be accessed with a GoTo’s are available as Advanced GoTo’s (or Execute Procedure in Navigation List Builder.) 
SmartList Advanced GoTo’s – Payables Transaction Inquiry, Purchase Order Inquiry, Receivable Transaction Inquiry, Sales Inquiry

Navigation List – Execute Procedure – SOP Inquiry, RM Sales Inquiry, RM Cash Inquiry, PM Transaction Entry Zoom or PM Manual Payments Zoom ** In GP2010 these are also available in SmartList Builder 

  • The name of the Window is the Name on the Top bar of the screen (This is the “Window Display Name”)
  • To determine the Product & Series:
    • use the Support Debugging Tool (I like to got to the Security Information, select by Menu and on the Menu Explorer on the Dictonary is listed on the right) For the series you will need to use trial & error
    • GP’s  Tools | Resource Descriptions | Window
  • To open the form with the related record(s), you need to reproduce the same steps, in the same order, that users do to pull up data on that form.  (So go do just that!)
  • Always build your Go To in steps and test for EACH step.  First open the form without any tasks, test.   Then add your first task, test.  And so on.  If you make a mistake you will know exactly where your problem is — nothing will happen when you select your Go To.
  • On SmartLists – I always test by selecting the Go To in the SmartList Go To dropdown (selecting the upper right window icon and clicking on my Go To name) rather than double-clicking on the list item to trigger the default GoTo.  This allows me to isolate problems with the Go To from problems with the Default Go To (Which, unfortunately has its own issues – to be discussed in a later post.)

For step-by-step screenshots of building a sample Go To in SmartList Builder, check out this post: http://janakirammp.blogspot.com/2009/07/smartlist-builder-building-goto.html

Sometime opening the form is easy but getting the form open with the data related to your SmartList data is a bit trickier.   That will be the subject of the next post in this series – Adding Tasks.

Until then, Happy building!
Devon

GoTo Series – Post 1 – screen shots on Building a GoTo

Devon Southall1 Comment

I have not been posting for a while, partially because I have been working (Stuck?) on a comprehensive series of posts about building Go To’s with advice and examples for most common screens… let’s just say that it has been a learning experience!

My first step was to google what was out in the blog sphere and newsgroups… and let me just say, many of you are rather frustrated by trying to build Go to’s. I have to agree… sometimes I have been perplexed too on how to make a Go To work.

One great post I found was a step-by-step look at building a pretty complex Go To – the Checkbook Register Inquiry by the Dynamics Blogger:

http://janakirammp.blogspot.com/2009/07/smartlist-builder-building-goto.html

Check it out! Next post I will dive into basic rules for adding Go To’s.

Make a GREAT list – checklist for Field Formating (common things to check)

Devon Southall1 Comment

I started this post 4 months ago, but never finished it.  In doing some Builder research for another post I stumbled across a blog from another SmartList Lover, Sara Corbett.  Her post SmartList Builder Etiquette is in the same vein as this post, but, interestingly, she lists different items than I do.  Compare the two posts and comment on what you think is important for a Great list…

What is the difference between a good SmartList and a great SmartList?  In my opinion, it is often in the details – mostly the Formating details. Here is a handy checklist of common things to check:

  • Field formating — press the blue arrow in the header left of Display Name to launch the field settings:
    • Note Index fields – check the box to pull the Note text
    • All Account Index fields – check the box to pull the Account Number
    • Phone & Fax numbers – select the dropdown for the Phone number formating
    • Social Security numbers – select the dropdown for Social Security formating
    • Date fields – check the box to display 1/1/1900 as a blank  (also can change the date formating, too)
    • Fields from SQL Views – make sure you turn off the $ on quantity fields and add % for percentage fields
    • Set Decimal places – especially for fields stored in GP as integers but displayed with decimals in GP (like the Shipping Weight on the Item master is stored as 100 where it is really 1.00, HR module stores most hour totals this way.)
  • Field Display Names – Add field names from the setup for user defined fields. On most Maintenance (“Card”) tables (yes, you have to type these EVERY time you add these tables, sorry!)   Look for “User Defined”
  • Dropdown Fields – make sure you enter values – See Drop Down Lists in SmartList Builder
  • Look for fields that will appear more than one on the final list, especially fields that will have different data from the various tables.  Consider turning off the Display checkbox, or at least, changing the Display name for some fields.

Also don’t forget to add useful Go To’s!

Happy Building!
Devon

Navigation List Builder – Let’s add some Actions!

Devon Southall2 Comments

OK so you have built your Navigation List (as per my prior post: Making a new Navigation List in Builder for GP 2010) 
Now let’s add some Actions:

Default Actions
Open your new Navigation List in your Navigation Pane and you will see two Action categories: Reports and Go To. 
Reports will contain the default action “Print This List”
Go To will contain the default action “Send to Excel”
(Both options are enabled when list items are selected and will print only selected items.)

Adding to the Go To action group
This group should open forms pre-loaded with the data related to the list item

  • Press the Actions Icon on the top bar for the Actions window
  • Press the Add Action button on the middle right > Select Open Form on the dropdown
  • Give it a description (name), Group of Go To, button size & priority
  • Rest of this window is identical to adding a SmartList Go To

If you want to add a Go To to the SOP Inquiry, RM Sales Inquiry, RM Cash Inquiry, PM Transaction Entry Zoom or PM Manual Payments Zoom windows, these are included in a different section (kind-of like the Advanced Go To’s on SmartList Builder):

  • Press the Actions Icon on the top bar for the Actions window
  • Press the Add Action button on the middle right > Select Execute Procedure on the dropdown
  • Give it a description (name), Group of Go To, button size & priority
  • Product is Microsoft Dynamics GP
  • Procedure – select from list of forms above
  • Bottom of window populates with the Parameters required to open this form.  Click on each line, Press Edit button on right and add Table and Field to find that value on your list.

Adding to the New action group
This group should open forms so that users can enter new transactions and/or cards

  • Press the Actions Icon on the top bar for the Actions window
  • Press the Add Action button on the middle right > Select Open Form on the dropdown
  • Give it a description (name), Group of New, button size & priority
  • Start like a SmartList GoTo with your Product, Series and Form, but typically you won’t want to add any tasks  (You won’t want to pre-load any data on the form.)

Adding to the Actions or Modify action groups
Actions group is for processes to the transaction or card on your list.  Modify is for changes to the transaction or card.  (For “Edit” under Modify use the Go To instructions above – unless that transaction is listed below)

  • Press the Actions Icon on the top bar for the Actions window
  • Press the Add Action button on the middle right > Select Defined Action on the dropdown
  • Give it a description (name), Group of Actions or Modify, button size & priority
  • Product is Microsoft Dynamics GP
  • Select the appropriate Series and Action from the list
  • Bottom of window populates with the Parameters required for this action. Click on each line, Press Edit button on right and add Table and Field to find that value on your list.

Here is an organized list of the Defined Actions available by Series (and the Group they are typically listed under in Navigation Lists):

HUGE NOTE – the Defined Actions of Print and Email are in Navigation List Builder but do not work.  Apparently, this is a known bug – they haven’t been programmed yet.  MS expects these to be fixed in GP 2010 SP2.  This is true for all document types.

  • System – Modify – Edit Note
  • Financial
    • Journal Entry
      • Actions – Back out, correct, copy, post
      • Modify – Delete
    • Batch – Actions – post, delete
    • Account – Modify – Open maintenance form
  • Inventory
    • Items – Modify – delete;  Site assign / unassign;  Currency assign / unassign
    • Item Price– Modify – adjust, copy, delete 
    • Inventory Transaction – Modify – Open transaction
  • Purchasing
    • Vendors – Modify –  apply / remove hold, delete, email settings
    • Purchase Orders
      • Modify  – delete, apply / remove process hold
      • Actions – print, email (see note above)
    • Payable Batch – delete, post
    • Payables transaction
      • Modify  – delete, post
      • Actions – print (see note above)
  • Sales
    • Customer
      • Modify – apply / remove hold, email settings, delete
      • Actions – Age account, assess finance charge ,print statement, NSF, waive FC
    • Prospect – Modify – delete
    • Salesperson – Modify – delete, inactivate, reactivate
    • Receivables Batch – delete, post
    • Receivables transaction
      • Modify  – delete, post
      • Actions – print, email (see note above)
    • SOP  Fulfillment Order – Actions – confirm pack, pick, ship
    • Sales Order Processing
      • Modify – apply & remove hold, delete, post, open entry (Edit)
      • Actions – allocate/fulfill, purchase, transfer
      • Actions (not working, see note above) – print Document, email, print Packing slip, print Picking ticket

Under the product Extender actions are avaliable for Delete, Duplicate, Open, Update value 

Let me know if you see any other issues with these actions and Happy Building!
Devon

How to get data in separate columns rather than rows in SmartList Builders (Aging, Sales by Months, etc)

Devon Southall2 Comments

This has been on my list of things to blog about – but when I saw this post in the Forums, I knew its time had come:

I have a client who is attempting to create a Sales report which shows sales by Customer.  They want the report to include a separate column for each month – based on Document Date.  I have attempted to create a calculation for each month (If/Then) but either don’t have the syntax correct or it is truly not going to work.

CASE
WHEN {{Sales Transaction History:Document Date} >1/1/2017…
 
Any ideas?????

This is something that seems to have become a very popular request in the last few years and I have always coded it in SQL. Since it requires a ‘group by’ in SQL code, I can’t imagine being able to do this in SmartList Builder. I would recommend coding this in a SQL view and basing the SmartList on that view.

I would differ 🙂 … This is easily accomplished in SmartList Builder:

First – Add a calculated field for each column of data 

  • In this scenario, one for each month – use the Month function to test:
    for January:
    CASE WHEN MONTH({Sales Transaction History:Document Date})=1 then {Sales Transaction History:Originating Document Amount} ELSE 0 END
    and so on…
  • For a list with aging in columns (inventory, AR or AP) 
    for example – the calculation for the “31-60 days” bucket for AR by Document Date:
    CASE WHEN
    DATEADD ( d , -30, GETDATE() ) > {RM Open File:Document Date}  AND
    DATEADD ( d , -60, GETDATE() ) <= {RM Open File:Document Date}
    THEN
    (CASE WHEN {RM Open File:RM Document Type-All} > 6
    THEN -{RM Open File:Current Trx Amount}
    ELSE
    {RM Open File:Current Trx Amount}
    END)
    ELSE 0 END
  • For other types of data – like quantities on hand – with Sites in columns, here is the calculation for the North site column:
    CASE WHEN {Item Quantity Master:Location Code}=’NORTH’ then {Item Quantity Master:QTY On Hand}
    ELSE 0 END

Next, unselect as Display any fields you don’t want to group by (or sum).  

In the scenario above we would want Year to be a group by field (or we will get all sales in January in history!).  So add a calculated field for year (Users can do a search filter on this field when running the list):

YEAR({Sales Transaction History:Document Date})

Now use the Options icon on the top to make this into a Summary SmartList – the way to “group by” in Builders.   Select all of your column calculated fields as Summary type “Sum” , your other fields as “Group by” and, in the words of my daughter, “There you go!”  

Learn more about summary SmartLists in my post: Summary SmartLists in Builder (Sales by Item for a date range, etc)

Happy Building!
Devon

PS – My apologies and thanks to the ladies on the original forum post!