Search Business Partner by phone number in Sales Order

[shak9828]

Dear experts,

Customer would like to search for BPs using their phone numbers instead of CardCode.

For example, now if you type in the CardCode of BP in the Customer field (in ORDR) and press TAB, it fills in the other fields.
Is it possible to add a Phone1 from Business Partner Master Data (OCRD) field at the header of Sales Order (ORDR) to use this to search for BP?
screenshot-2.png

[Abdul · Accepted solution]

Yes its possible, Create a UDF on Sales Order And Deploy a FMS to show phone 1 of relevant customer

FMS will be Like: Select Phone1 from OCRD where CardCode = $[ORDR.CardCode]

[shak9828 · Accepted solution]

Abdul,

Thank you very much! You guided me to a feature that I did not know existed.
I watched some videos on YouTube
https://www.youtube.com/watch?v=x2wbdJ2rlnQ
https://www.youtube.com/watch?v=lCi8B9EYeag - this particular video is what I want but with phone number and to do the opposite i.e. when i search for Phone1 then CardCode should fill in with respective value.

I tried the query from the video, it also did not work.

I tried your query but some errors popped up

I'm using SAP Business One 9.3 HANA PL04

If you could help me, I would be grateful!

Thank you in advance,
Shak

[Abdul · Accepted solution]

Save this query it will not execute in query generator because it has parameter to give value on runtime according to customer. Just Past it in query generator and then save it in Queries folder.

Then open Sales order go to Tools > customization tools > User defined values Seteup > then select 3rd option in small window after that click open saved queries and select the query you saved in previous setup then select auto refresh option based on Customer/Vendor Code click update then try to select customer code on Order and it will work automatically

Select "Phone1" from OCRD where "CardCode" = $[ORDR."CardCode"]

[shak9828 · Accepted solution]

Abdul,

Thank you. But i need the opposite to happen.

In UDF "Phone Number" inside the ORDR, I need the search button and when that button is clicked all the Phone1 from OCRD comes up and when one is chosen, the CardCode in the ORDR is filled.

Thank you,
Shak

[Abdul · Accepted solution]

Deploy this in Customer Code field where you want to select customer Select "CardCode" from OCRD where "Phone1" = $[ORDR."U_Phone1"] and create a UDF and deploy following query in UDF (Select "Phone1" From OCRD)

[shak9828 · Accepted solution]

Thank you! This is what I was looking for.

[Abdul · Accepted solution]

Please mark the correct answer and close this thread

[gonzalogomez]

Yes.

With a formatted search you bring the phone to a user field from the IC. Edit the form and add the field.

You can also create a query that makes that search.

SELECT T0.[DocNum] FROM ORDR T0  INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode WHERE T1.[Phone1] =[%0]

then in the field Document No. defines a formatted search towards that created query.

When you select the phone number, you transfer the document number. That's right, you will not see the phone number in the form.

[shak9828]

Dear Gonzalo,

Thank you very much for your reply.

Can you help with a FMS Query in SAP Business One 9.3 HANA to search for Phone1 and then automatically CardCode is filled in.

Thank you,
Shak