Does anyone know how to have the most recent call log of a quote populate in a BAQ?

Epicor ERP 10
, ,
1

Hello,

I am trying to set up a BAQ that pulls Multiple quotes and displays only the most recent call log associated with the quote. I have gotten to the point of having all the call logs for a quote populate but I can’t seem to get just the most recent entry to show. Any thoughts on how to possibly solve this issue would be greatly appreciated.

Best,

2

What field are you using to determine the “order” of the call logs?

When I need to find the most recent of something. I’ll make a sub-query that returns the Max of the field I’m using to determine the order (Like TranNum in the PartTran table), then join that sub-query to the main one.

For example, to find that last part tran of each part by tran type:

  1. Make a sub Qry of the PartTran table with display fileds
    a. PartNum (Grouped By)
    b. TranType (Grouped By)
    a. Calculated field of Calc_Max_TranNum = MAX(TranNum) (NOT Grouped By)

  2. Add that sub Qry to the main one (which has the PartTran table in it) relating the Calc_MaxTranNum field to TranNum of the PartTran table in the main qry

3

I have tried using the multiple date field like “last date” or “orig date” but neither of those worked when i tried to use the Max function. Also tried using the Call Log Sequence but i couldn’t get that to work either. I will try to do something similar to the example you posted. Thank you for your help.

4

So the first problem is that you have no way of determining which call log entries are more recent than others?

I’m not at all familiar with the Call Logs. Can a call log entry be updated? or can only new entries be made?

5

The problem is that i cant have my BAQ only display the most recent call log that was entered.

You can update call logs but my company has decided to avoid doing that and we have gone with creating new call logs each time we complete a phone call.

6

It looks like the CallSeqNum is a value that identifies the order of the entries for a specific Combo of key fields

Make a test query with just the CRMCall table, with the following displayed columns:
image

Here’s the details of the Calc field
image

And run it

You might want to add some table criteria to limit it to the calls you want.

I think you’d see

Company RelatedToFile Key1  Key2 Key3  MaxSeq
MC      QuoteHed      12345                    3
MC      QuoteHed      12367                    1
MC      QuoteDtl      12399    5               4

If:

  1. Quote 12345 had 3 calls entered (against the quote head)
  2. Quote 12367 had 1 call entered (against the quote head)
  3. Quote 12388 had 4 calls entered (against quote line 5)
7

Thank you for your help again. Unfortunately, that didn’t seem to work either. I think the issue is that the table treats each individual call log as its own string of data so when you ask the BAQ to look at the max sequence it looks at each individual call log. This means that a Sequence 1 call log will have a max sequence of 1 even if there are multiple call logs. I will try to build the BAQ again with your above example. I will let you know if it works on my second try.

8

We group by CallQuoteNum (or with criteria RelatedToFile=‘QuoteHed’ and then group by Key1 only) and then get MAX(CallSeqNum). You do have to use only those columns, though, and then join back to the full table to get the rest of the data you want.

If you include and group by all the keys then it won’t work.

9

This worked thank you so much.

10

Thank you for your help I Really appreciated it