Inspection Report --- FINALLY!

[jkane (John Kane)]

Are you not able to import the files? I thought the files would be updated on load

[mackenziem (Mackenzie Miller)]

@jkane

Ok, not sure if other setups will work but I want to explain mine so if someone has a different one they may be able to adjust. First is to create the Inspection Attributes and create an attribute for EVERY Table Field Name. Make the Attribute ID the same name as the Table Field Name. See below

Hi John, thank you for pointing me here. At this section, do you mean you create a new Inspection Attribute for every Table Field Name, for example, When I add a new attribute and pick the Attribute type ‘Numeric’ and the Table Field Name dropdown has Numeric001-Numeric200 I need to create an attribute for each? If that is the case, I assume DMT would make this much easier than clicking in the GUI

[jkane (John Kane)]

Yes, that is exactly what I did. I probably entered 1 to export out as a DMT template so I could know what I needed to enter.

[mackenziem (Mackenzie Miller)]

I’m making that DMT now. The interesting thing is ComboBox and Character sharing the Table Field Name ShortCharXXX. Do these conflict if you’re creating an Inspection Plan that uses CheckBox001 and Character001 because they would both be using ShortChar001?

[jkane (John Kane)]

That is probably a mistake. I’m no longer at that company, so I can’t check what I did. But I agree that does not sound correct.

Where are you seeing that so I can double check the files?

[mackenziem (Mackenzie Miller)]

I am looking in the Kinetic view for creating an Inspection Attribute. I have also BAQ’s the InspAttr table to double check, because DMT says AttributeUse is required, but the BAQ returns nothing.

Here is a screenshot of Attribute Type with the FieldName that shows both of them sharing the same Table Field Name

[jkane (John Kane)]

Thanks for jogging my memory!!

Yes, Combo Box and Character both use the ShortChar fields. What I did was assign ShortChar001-ShortChar100 to combo box and ShortChar101+ to character. Both of those field types use the ShortChar fields to store their values. You should not double up, you should make an estimate of how many drop downs you think you will need and associate as required.

Make sense?

[jenjen7981 (JSmith)]

Hi John, Can you help me get past this new error. Text: Bad SQL statement. It doesn’t like the FOR per my logs.

[jkane (John Kane)]

What version are you on?

[jenjen7981 (JSmith)]

10.2.700.40

[jkane (John Kane)]

You need single quote, not double.

SubQuery3.InspResults_SpecID FOR XML PATH('') expression

[jenjen7981 (JSmith)]

Ice.Common.EpicorServerException: Incorrect syntax near the keyword 'FOR'. ---> System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'FOR'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at Ice.Blaq.Execution.QueryExecutor.ExecuteAndFillDataSetInternal(IDbConnection dbconn, QueryInfo queryInfo, DataSet resultDataset, Action`2 perfLogger) in C:\_releases\ICE\ICE3.2.700.40\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutor.cs:line 118
   at Ice.Blaq.Execution.QueryExecutor.<>c__DisplayClass3_0.<ExecuteAndFillDataSet>b__0(IDbConnection dbconn) in C:\_releases\ICE\ICE3.2.700.40\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutor.cs:line 37
   at Ice.Blaq.Execution.QueryExecutionHelper.DoJobWithObject[TObj](Func`2 theJob) in C:\_releases\ICE\ICE3.2.700.40\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutionHelper.cs:line 195
   --- End of inner exception stack trace ---

[jenjen7981 (JSmith)]

It is single, I sent then wrong screenshot.

[jkane (John Kane)]

Looks like FOR XML PATH was taken away in 10.2.7. You will have to use STRING_AGG.

Unfortunately, I don’t know how to do that. I would search the site for STRING_AGG to see if someone explains it here. Or search the interweb. Sorry

[jenjen7981 (JSmith)]

Ok, thank you for the direction. Hope you have a great day!

[jenjen7981 (JSmith)]

For anyone who runs into this…see this post. You all ROCK!

FOR XML PATH versus STRING_AGG

Hi Everyone! I hope you all are doing well. Today I am facing a problem in our Pilot environment of Epicor. BAQs that utilize the FOR XML PATH command, are now broken. A quick response from Epi techs indicates that this is a known issue, and since we don’t yet have SQL 2017 or higher we can’t use STRING_AGG either. Has anyone else come up against this issue recently? Is there a simple process for replacing FOR XML with STRNG_AGG? In our BAQs we create a subquery that returns a list of the val…

[jenjen7981 (JSmith)]

Must not be a lot of people using this, or if they are they aren’t having all the problems I am trying to get it to work. I am past all my issue’s and finally ready to I think preivew some data. Step four says it’s not a BAQ Report, then you say it is. Am I using the InspColumns BAQ to create the BAQ Report in designer. I’ve worked with Epicor on many reports but NOTHING at this level. Again, thank you for your time and help.
BAQ Report or URL. Sorry for my ignorance.

[jenjen7981 (JSmith)]

And I mean the Enhaced module

[jkane (John Kane)]

No, you need to manually create an RDD and add the BAQs as the data source in that RDD.

[jenjen7981 (JSmith)]

Then it get’s added to the menu as a URL Link report? I do have your RDD loaded. Just not sure how to get it in the menu and to fire, I’ve never had to do a report this way. Not much I could find on it either, so you are my only source hehe.