18,265
Hi,
I have view which gives aged of outstanding Invoices. I need to build query in Collection Management which will show only Invoices which is aged <=45.
How to use Advanced Query in Collection Management.
Your earliest reply is highly appreciable.
Thanks
Sandip
*This post is locked for comments
Sarah,
Appreciate. That didn't help. I have open support request with Collection Management. I will update you status.
Thanks much.
Sandip
I would try clicking on the box that says: Documents in Query Date/Type ID Restriction Only.
Hi Sarah,
Base on following example:
Collection Plan:
it should show only one Invoices why it is showing all others.
Thanks
Sandip
Hi Sandip,
What I think you may be happening is you are seeing all the customers that have an invoice open on their account that is at 45 days but they may have invoices that are older or more recent than that as well.
Although your query is based on 45 days only, you will see any and all invoices open for any customer that has an invoice at 45 days.
For example say a customer has 4 invoices open on their account and you want to run your query on the 45 days but the customer has the following invoices dated 5/1/15, 5/3/15, 5/30/15, and 4/28/15 open on their account. The only invoice you are looking for would be invoice dated 5/3/15 as that would put it 45 days out from today (based on document date, not due date). Am I correct in how the query pulled for you?
When you run the report it tells you how many items meet your qualifications. When I ran my report it told me that there were 14 documents that fell in the day range I was looking for.
I apologize if my responses are lenghty...
Might I suggest running a smartlist or smartview report instead?
Sorry it should be <=45 Days.
Thanks
Hi Sarah,
Appreciate for your reply. Receicvable aging was base on Document Date. Even though I changed to Due date it should show only Invoices age 45 if my from Days and To Days is 45 but it display all outstanding which is >= 45.
How to show only those invoices which is aged exactly 45.
Thanks
Sandip
Hi Sandip,
If you have your receivables set up to age by document date then you could set your query up as such...
I was wondering if you might be able to provide a little more information. Are all your terms the same (i.e. N30) or do you have all different Net terms? The reason I ask is becuase that would determine how you might want to run your query.
For example, we have our aging buckets set up like so:
1-30
31-60
61-90
90+
Becuase we have a wide variety of terms we found it best (and easiest) to set it up to age the invoices by due date as opposed to document date. By doing it this way we are able to run our queries and pull all invoices that are past due even by 1 day regardless what the terms are.
I hope this helps. Please let me know if you need me to clarify anything or if further information is needed.
Thank you,
Sarah Case
Hi Redbeard,
Appreciate for video, I can create simple query. I need all outstanding invoices which is aged base on Due Dates more than 15 Days , 45 Days and 65 Days. Three different query
I am not able to do it with Std Query. It has option Advance query but don't have any documentation how to use it.
Thanks
Sandip
Sandip -
Here is a link to a video on the query feature in Collections Management.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (