BAQ - Does phone number in parameter match any phone numbers stored - Search multiple tables

Epicor ERP 10
1

Hi

Been tasked with creating a BAQ where a user can type in a known phone number and the BAQ returns any matches to stored customer or customer contact numbers. (Could be the number for the main customer office or a contact’s mobile).

I’ve entered the parameter so the user can type in the phone number, and have set criteria on the CustCnt table to return any records where the phone number fields are like the number being searched.

But how do I set it to search BOTH the customer and custcnt tables for a match?

Ideally I want to see if the phone number is LIKE

Customer.PhoneNum
OR
CustCnt.PhoneNum
OR
CustCnt.CellPhoneNum
OR
CustCnt.HomeNum
OR
CustCnt.AltNum etc.

image
image.png1920×1040 36.4 KB

image
image.png1920×1040 63 KB

2

Union SubQueries on the CustCnt table for each phone number field and set the table criteria for each query to the parameter…

image
image.png876×664 23.9 KB

image
image.png876×664 44.2 KB

3 Likes
3

Thank you Brian!
I had tried subquery but put it all in one instead of separately.
Have tried your suggestion and it works, thank you.

1 Like
4

This is interesting! I bet this would be helpful to our users. I would like to add this in a Quick Search but I’m not sure how to accomplish it using the Parameter function. Karen, do your users have to go into BAQ, pull in the query, hit analyize, then type the Phone#? This seems like a lot of work, plus I don’t want to give all users access to BAQ. Is there any way to simplify this workflow?

5

Hi Lacey
I’m aiming to make it work in a quick search eventually but thought I could provide the BAQ as a quick interim solution. It’s for 1 user who sometimes needs to look through calls made by the cold calling sales team. Agree it’s not ideal for multiple user use.
I’d be keen to know how to make better use of it also!

6

I will keep trying. I swear I saw someone demo this at Insights and then used it in a Quick Search, I’m still trying to figure out how they did it. I will try to update this post if I happen to figure it out!

7

Interesting idea. Out of curiosity, are all of your phone numbers the same
format? Meaning, (###) ###-####, ###.###.####, vs. #########, etc…

If not, what’s the best way to parse just the numbers out to compare with?

OT - With data imported from our old ERP, and 10 year with Epicor, ours are
all over the map and it’s annoyed me for awhile…Would love to correct
then all (maybe DMT?) and inforce a mask for future entries.

Thanks,

Norman Hutchins
Systems Administrator
Howell Laboratories, Inc.

8

@nhutchins, we had a similar challenge and DMT’d a standardized phone numbering map too.

9

You can add the BAQ to a dashboard and add the dashboard to a menu somewhere. Then you don’t have to have BAQ access and it’s easier for the end user. Let me know if you need help setting up a dashboard.

Also, with dashboards you can add them into actions menus via customizations, or right click functionality. Lots of options and ways to make the data more accessible.