I am running a BAQ and need to pull the column “InactiveToDate” from the “AC_SegAccom” table to display on a dashboard however when I go to select this column it doesn’t appear. I’ve checked the extended properties and it exists inside the table
When I go to select the column inside the BAQ it isn’t there.
Is there any reason as to why the column exists but doesn’t appear for selection in a BAQ?
If you also sort the BAQ list by Column Name, are you then able to find it? The columns in BAQ appear by default in the order in which they were added to the table.
It would be very strange for it to not be there - especially as it’s not a UD field. If it was UD fields in query I’d say to regen the data model, recycle app pool and ensure you’ve closed and re-opened Epicor.
Also, while you have the list selected, press the first letter of the field. You can jump around that way.
That field doesn’t seem to exist on the table.
It may be a calculated field that is generated at runtime.
You’ll likely need to calculated it yourself for your BAQ.
In the data dictionary viewer under the AC_SegAccom table it doesn’t exist either:
It is not a calculated field to my knowledge. In the BAQ I have multiple calculated fields and it is not one of them.
I did notice on the extended properties view the column’s external box is ticked. There are many columns that are externally ticked and don’t appear in the data dictionary.
I have requested a Data Regeneration however I have to populate the new columns under the AC_SegAccom table but they aren’t showing up so this process can’t go ahead until I’ve created the new columns. Is it something to do with these columns being external, if so how do I manage to put them under the AC_SegAccom.
I’m guessing you are using SLS - never seen it in person, but from the field names…
Sometimes a field is added to a temporary table by Epicor, before being introduced into the schema properly in the next upgrade. They’re trying to limit any breaking changes.
Check in the Ice.PatchFld table - could be that the columns live in there, tied back to the record in AC_SegAccom table via SysRowID.
Yes I am using SLS.
There is a major update happening today so that could potentially be why it isn’t appearing.
This is what the extended properties shows:
This is in the data dictionary:
It doesn’t seem like they’re connected, I am unfamiliar with this though.
Create yourself a BAQ to look at the contents of the Ice.PatchFld table.
If my guess is correct, you’ll have entries where TableName = “AC_SegAccom” and FieldName = “InactiveToDate”
I don’t mean to say it’s a calculated field in your BAQ.
It’s “calculated” in that it’s not actually a field on the table you’re looking at. Epicor is populating the value inside the BO logic. Sometimes these values are calculated from other values. (A common example is Order Totals. Epicor will have a column in the dataview that has a rolled up total, but they don’t write it back to the DB.) Sometimes the value is simply pulled from a different table.
Okay,
Understanding the architecture of how epicor writes to the DB etc is something I am not very familiar with. I have a date dropdown here:
I thought the AC_SegAccom.InactiveToDate would be housed as a table.column value.
What would be the best way to save/capture the date if this value is captured inside the BO logic and display it back on my dashboard?
Is there anything that needs to change inside this to make it available?
Does this have anything to do with the External checkbox being ticked in the field list?
I have below the AC_SegAccom joined to the Ice.PatchFld
The only columns I can see that are a date type is the DataDate column.
The table relations are based on the below. Is there a way I could assign the InactiveToDate to equal the DataDate column in the Ice.PatchFld table?
You may want to consider creating a subQuery for the patchfld records.
You can create Calculated fields with names and types you want.
Eventually, PatchFld entries will be migrated to the new table. Eventually. And this will break your BAQ. Putting it in a subQuery will give you one place to fix it when it does.
Create yourself a fresh BAQ, display all columns, and see what you find. You’ll either find that there are rows with the matching table and field name or you won’t. No point trying to join to that table in BAQ until we know whether it contains the data you’re interested in.
I’m doing this blind without access to SLS.
Yes, as already stated, use a calc field, or just label it so you know.
Let’s back up though. Did you find a field in patch field that matches that tablename and fieldname?
Can you run it again but without any criteria. That’s currently showing no records and supporting the idea that it’s not a DB field and instead it’s something calculated in the business object.
But let’s confirm that there are no rows in PatchFld that match.
Appears to be no InactiveToDate field, these are all the AC_SegAccom fields that returned with no criteria.
Run it with the field name too so you can see if any fit. If it’s not there, it’s either pulled from somewhere else or calculated from somewhere else.
Honestly, I’ve always seen fields in PatchFld show up in BAQs, just not in code via DbContext or, gulp, SSMS
A BAQ for zDataFields looking for fields with the string ‘inactive’ in it does not show the field you are looking for. It must be calculated.
I missed previously if you stated the business problem you’re trying to solve. Maybe someone here knows another way to get what you need.
Okay so if it’s calculated is there a way I can access this inside the BO and potentially do something there?
The business problem is I need to capture the data from this date box below:
And by utilising the BAQ display that field on a dashboard. It’s simple but I can’t achieve that without this InactiveToDate field being captured.
