- SAP Managed Tags:
FORMATTED SEARCHES ARE GREAT, BUT THEY CAN BE TROUBLE ALSO.
Formatted Searches (FMS) are another fine tool you can use in SAP B1. You can read up on Formatted Searches with some SAP B1 documentation, or maybe you can buy Gordon Du's book titled "Mastering SQL Queries for SAP Business One" to check out what he says about Formatted Searches in Chapter 6.
https://www.amazon.com/Mastering-SQL-Queries-SAP-Business/dp/1849682364
After a little while you and your colleagues have enough courage to start writing your own FMS. Maybe an FMS to pull over important fields into your BP Master Data window. Maybe another to auto-populate the Tax Code on all Marketing Documents, or maybe an FMS to create a list showing all open Sales Orders while Sales Personnel are entering a Sales Order.
But then something happens which I have seen a large number of times over the years. No one has kept any kind of documentation on the Formatted Searches, someone changes a business requirement which might impact the FMS, and then you sit wondering "where are all these Formatted Searches being used???". And more importantly, "how can I change the settings (or remove) them ALL at one time???". It can get very confusing.
So this might be an example of your quandry. There are a about 45 Formatted Searches and you have no idea who put them there, and even worse, where they are used in SAP B1. You could go to every document in SAP B1 and create a hand-written list of all the Formatted Searches and associate fields. That would take you about four weeks, and drive you bonkers as you go along trying to keep everything straight.
OR -
You could use this little SQL to get the information a bit quicker in about ten minutes. All the attached SQL does is read a table to tell you where the Formatted Searches are in your Query Manager, and looks at another table to see some basic pieces of information associated with FMS, which might prove useful (i.e., what query is involved in the FMS, what document it is on, what field it is assigned to, etc.). You might not need all of the information, so you can comment out what you do not need. But I would urge you to keep those commented lines as you might need them in the future. So much easier to keep them around, then trying to re-create the wheel months down the road.
IMPORTANT CAVEAT - I hesistate to count how many "Form IDs" there are on SAP B1. I have a list which is 18 pages long. There is probably a list somewhere which could help you out. Maybe someone has a link to a newer list???
Here is one SCN from some time ago.showing some (repeat some) of the Form IDs, but as the author says "it is a small list". At least small compared to the one I have.
https://blogs.sap.com/2013/07/09/sap-business-one-form-types-and-object-types/
But, I added a line in the SQL which will tell you if you need to find the Form ID ("ELSE ' ***RESEARCH*** ").
There is also a limit of how many Form IDs you can put in the SQL. Not sure what the limit is - I tried to copy all of the Form IDs in one time and the system balked big time.
THE SQL.
NOW TO THE SQL RESULTS
And here are the results of the SQL. Take a small look to see what it has, along with the notes I put on there after runninig it.
Try this SQL and see how many different FMS are included in your system, and more importantly, where they are used. I can only imagine how difficult it is to keep a list like this manually. Hopefully, this makes it a bit easier for you!!!
SAP B1 Forum SQL 04 2019 01 12 Formatted Searches and How to Find Them
Formatted Searches (FMS) are another fine tool you can use in SAP B1. You can read up on Formatted Searches with some SAP B1 documentation, or maybe you can buy Gordon Du's book titled "Mastering SQL Queries for SAP Business One" to check out what he says about Formatted Searches in Chapter 6.
https://www.amazon.com/Mastering-SQL-Queries-SAP-Business/dp/1849682364
After a little while you and your colleagues have enough courage to start writing your own FMS. Maybe an FMS to pull over important fields into your BP Master Data window. Maybe another to auto-populate the Tax Code on all Marketing Documents, or maybe an FMS to create a list showing all open Sales Orders while Sales Personnel are entering a Sales Order.
But then something happens which I have seen a large number of times over the years. No one has kept any kind of documentation on the Formatted Searches, someone changes a business requirement which might impact the FMS, and then you sit wondering "where are all these Formatted Searches being used???". And more importantly, "how can I change the settings (or remove) them ALL at one time???". It can get very confusing.
So this might be an example of your quandry. There are a about 45 Formatted Searches and you have no idea who put them there, and even worse, where they are used in SAP B1. You could go to every document in SAP B1 and create a hand-written list of all the Formatted Searches and associate fields. That would take you about four weeks, and drive you bonkers as you go along trying to keep everything straight.
OR -
You could use this little SQL to get the information a bit quicker in about ten minutes. All the attached SQL does is read a table to tell you where the Formatted Searches are in your Query Manager, and looks at another table to see some basic pieces of information associated with FMS, which might prove useful (i.e., what query is involved in the FMS, what document it is on, what field it is assigned to, etc.). You might not need all of the information, so you can comment out what you do not need. But I would urge you to keep those commented lines as you might need them in the future. So much easier to keep them around, then trying to re-create the wheel months down the road.
IMPORTANT CAVEAT - I hesistate to count how many "Form IDs" there are on SAP B1. I have a list which is 18 pages long. There is probably a list somewhere which could help you out. Maybe someone has a link to a newer list???
Here is one SCN from some time ago.showing some (repeat some) of the Form IDs, but as the author says "it is a small list". At least small compared to the one I have.
https://blogs.sap.com/2013/07/09/sap-business-one-form-types-and-object-types/
But, I added a line in the SQL which will tell you if you need to find the Form ID ("ELSE ' ***RESEARCH*** ").
There is also a limit of how many Form IDs you can put in the SQL. Not sure what the limit is - I tried to copy all of the Form IDs in one time and the system balked big time.
THE SQL.
--V-MS Listing of All Formatted Searches on System with Forms Ver 1 ZP 2018 01 09 SOL
--DESCRIPTION: SQL lists out where Formatted Searches are used and in which document. This SQL must be customized for each Customer Usage since the SQL can get to be over 18 pages long and does not fit into the Query Generator. Full copy goes to Form ID 20250 which is Purchase Analysis by Vendor Groups. Start out with basic documents and then expand as Formatted Searches are created.
--USAGE: IT Department
--AUTHOR(s):
--Version 1 Zal Parchem 20 July 2015. Added to SOL Query Category on 09 January 2018. Need to find form IDs 85 and 134.
--Version 2 Zal Parchem 09 January 2018. Modified for SOL to show Refresh information.
SELECT
T0.CatName AS 'Query Category',
T1.IntrnalKey AS 'Query Internal Key',
T1.QName AS 'Query Name',
T2.Refresh AS 'Auto Refresh',
T2.FrceRfrsh AS 'Refresh Regularly',
T2.ByField AS 'If Refreshed by Header Field',
CASE
WHEN T2.FormID = '85' THEN 'Pick List'
WHEN T2.FormID = '133' THEN 'A/R Invoice'
WHEN T2.FormID = '134' THEN 'Business Partner Master Data'
WHEN T2.FormID = '139' THEN 'Order'
WHEN T2.FormID = '140' THEN 'Delivery'
WHEN T2.FormID = '141' THEN 'A/P Invoice'
WHEN T2.FormID = '142' THEN 'Purchase Order'
WHEN T2.FormID = '143' THEN 'Goods Receipt PO'
WHEN T2.FormID = '146' THEN 'Payment Means'
WHEN T2.FormID = '149' THEN 'Quotation'
WHEN T2.FormID = '150' THEN 'Item Master Data'
WHEN T2.FormID = '179' THEN 'A/R Credit Memo'
WHEN T2.FormID = '180' THEN 'Returns'
WHEN T2.FormID = '181' THEN 'A/P Credit Memo'
WHEN T2.FormID = '182' THEN 'Goods Returns'
WHEN T2.FormID = '60110' THEN 'Service Call'
WHEN T2.FormID = '65300' THEN 'A/R Down Payment'
WHEN T2.FormID = '65301' THEN 'A/P Down Payment'
WHEN T2.FormID = '65302' THEN 'A/R Invoice Exempt'
WHEN T2.FormID = '65303' THEN 'A/R Debit Memo'
WHEN T2.FormID = '65304' THEN 'A/R Bill'
WHEN T2.FormID = '65305' THEN 'A/R Exempt Bill'
WHEN T2.FormID = '65306' THEN 'A/P Debit Memo'
WHEN T2.FormID = '65307' THEN 'A/R Export Invoice'
WHEN T2.FormID = '65308' THEN 'A/R Down Payment Request'
WHEN T2.FormID = '65309' THEN 'A/P Down Payment Request'
ELSE ' ***RESEARCH***'
END AS 'Form Description',
T2.FormID AS 'Form ID Numb',
T2.ItemID AS 'Area/Header Field FMS is Assigned',
T2.ColID AS 'Column field FMS is Assigned',
T2.FieldID AS 'Auto Refresh Field',
T1.Qtype AS 'Query Type',
T1.QString AS 'Query'
FROM OQCN T0
INNER JOIN OUQR T1
ON T0.CategoryId = T1.QCategory
INNER JOIN CSHS T2
ON T1.IntrnalKey = T2.QueryId
WHERE T0.CategoryId != -2
ORDER BY T1.QName
NOW TO THE SQL RESULTS
And here are the results of the SQL. Take a small look to see what it has, along with the notes I put on there after runninig it.
Try this SQL and see how many different FMS are included in your system, and more importantly, where they are used. I can only imagine how difficult it is to keep a list like this manually. Hopefully, this makes it a bit easier for you!!!
SAP B1 Forum SQL 04 2019 01 12 Formatted Searches and How to Find Them
7 Comments
