I’m working on creating a BAQ. I have been saving often, but after creating around my 5th sub-query I suddenly get this error when I am trying to click the save button:
Column ‘Company, QueryID, SubQueryID, RelationID’ is constrained to be unique. Value ‘, , e9afa764-51a0-4ca8-91d1-fa94beca7627, dbe755e9-5898-441b-8217-914497fd2dcd’ is already present.
[above message repeated 9 times]
ForeignKeyConstraint Relation2Field requires the child key values (, , e9afa764-51a0-4ca8-91d1-fa94beca7627, b2c98065-bf93-4a31-8a0a-8bc656ef5a04) to exist in the parent table.
[original message repeated 5 times more]
ForeignKeyConstraint Relation2Field requires the child key values (, , e9afa764-51a0-4ca8-91d1-fa94beca7627, b2c98065-bf93-4a31-8a0a-8bc656ef5a04) to exist in the parent table.
[original message repeated 5 times more]
I have no idea what it’s going on about, only that this is apparently preventing me from saving my BAQ, which is pretty important.
When I try to get more info I get this additional info:
Exception caught in: Ice.UI.BAQDesignerEntry
Inner Exception Message: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Program: Ice.UI.BAQDesignerEntry.dll
Method: QueryDesignToDynamicQuery
Client Stack Trace
at Ice.UI.App.BAQDesignerEntry.BAQTransaction.QueryDesignToDynamicQuery(BAQDesignerDataSet queryDesign, DynamicQueryDataSet dynQuery)
at Ice.UI.App.BAQDesignerEntry.BAQTransaction.PrepareAnalyzeDs()
at Ice.UI.App.BAQDesignerEntry.BAQTransaction.SaveQuery(String queryID, Boolean forceDialog)
What is it going on about? Why won’t it save my BAQ?
I noticed after this that some of my SubQuery Criteria where missing. As I started adding them back I noticed that after entering the Table, Field, and Filter Value, suddenly the Field changed to something other than what I set it to, requiring me to fix that as well. However, fixing all my criteria did not make the error go away. Then I went to actually use the subquery where it was needed, but this also did not make the error go away. I’m also not able to test or analyze the query due to this error.
I also confirmed by opening the query in a second instance of the BAQ Designer that none of my work since I started creating the last subquery is being saved.
I found a few minor errors in some calculated values by copying the query phrase and trying to run it directly under SQL Server 2016. After fixing these the SQL query runs fine directly, but I still am unable to save it in the BAQ Designer. Trying to copy the BAQ or export it also fails, so I am unale how to avoid data loss when Epicor gets disconnected.
It took about an hour’s worth of work, but I was able to rebuild the BAQ back from the last save point - which was basically reconstructing a single subquery (comparing side by side from the original instance BAQ Designer which wouldn’t save), saving after every single change I made. I noticed that the BAQ Designer likes to play several pretty mean tricks on the user. For instance, it would frequently forget that I had sorted from A-Z and instead display in non-sorted order. Every time i saved it would switch from the current tab I had selected to the Table Relations tab. Also, every single time I entered a new SubQuery criteria, it would change the field I entered to something else. In conjunction with the previous trick, this means that if I hit save too soon I wouldn’t even notice that the field value had changed unless I switched back to the tab I had been on. This bit me later on when I was trying to figure out why my query wasn’t working and wasted another 15 minute going over my query with a fine toothed comb until I found (at the very end of the query!) that inexplicably it was comparing Part.AESExp instead of Part.InActive like I originally commanded.
The lesson from all this is apparently that you should hit save after every single little thing you do in the BAQ Designer as well as double check after each step that it didn’t just undo something you told it to do. I ran into the above constraints violation again later but fortunately I just had to refresh to get back to the saved version and redo the one thing that I had just done that had freaked it out for some reason.
After you have been burned a couple of times, it becomes instinctive to hit that save button constantly AND check for the blue bar that shows the save is actually occurring. The other thing that happens is you are hitting save and it is not actually saving anything. You can even run the baq and it will execute with the changes and you refresh or come back later and what you built is just . . . gone. No matter how many times you clicked save.
Especially like errors like this - can’t write the parent value because it exists, then can’t write child values because the parent value doesn’t exist. All I can think is that the client’s copy of the parent value drifts sometimes.
