Field Not Found error, but FieldNumber says it exists

[#1]

Yet another Field not found issue.

The attribute is available in a resident table and I proved that by using the FieldNumber trace statement.

If I comment out the ProductGroupTurns line in the load stmt, I get another field not found error for InvValPreviousMonth1 (a few lines lower)

I'm using similar code a bit earlier in the app and that does not cause any issues.

Any thoughts on what I should do to debug this issue?

John

1/20/2015 2:52:12 PM: 2082      Let vDebug = FieldNumber('ProductGroupTurns','InvValuationTable')

1/20/2015 2:52:12 PM: 2083      TRACE ----------------- ProductGroupTurns FieldNumber: 4 - Inside Sub

1/20/2015 2:52:12 PM: 2083      ----------------- ProductGroupTurns FieldNumber: 4 - Inside Sub

1/20/2015 2:52:12 PM: 2086      UpdateOMInvHistory:

1/20/2015 2:52:12 PM: 2087        Load

1/20/2015 2:52:12 PM: 2088          WHGroup, 

1/20/2015 2:52:12 PM: 2089          ProductGroupTurns   AS ProductGroup,  

1/20/2015 2:52:12 PM: 2090          2015 AS FYear,

1/20/2015 2:52:12 PM: 2091          3 AS GlPeriod,   

1/20/2015 2:52:12 PM: 2092          '' AS PeriodType,

1/20/2015 2:52:12 PM: 2093          ''                  AS ValueType,

1/20/2015 2:52:12 PM: 2094          Evaluate(SUM(InvValPreviousMonth1)) AS Value,

1/20/2015 2:52:12 PM: 2095          Dec     AS CalendarMonth,

1/20/2015 2:52:12 PM: 2096          201503       AS YM,

1/20/2015 2:52:12 PM: 2097          'Inventory' AS MetricType,

1/20/2015 2:52:12 PM: 2098          '' AS GoalTurns

1/20/2015 2:52:12 PM: 2099        Resident OMMetricsHistory

1/20/2015 2:52:12 PM: 2100        WHERE

1/20/2015 2:52:12 PM: 2101          ProductGroupTurns <> 'Not Categorized' AND

1/20/2015 2:52:12 PM: 2102          ProductGroupTurns <> 'MD-Coatings'

1/20/2015 2:52:12 PM: 2103        Group by ProductGroupTurns, WHGroup

1/20/2015 2:52:12 PM:           Error: Field not found - <ProductGroupTurns>

[Anonymous]

John,

The error messages sometimes are misleading.  I see a problem here:

Dec     AS CalendarMonth

Dec is expected to be a field.  If it is a value, it should be

'Dec'     AS CalendarMonth

Next, I never used "evaluate" this way.  Likely it is not a problem but why don't just

SUM(InvValPreviousMonth1) AS Value

Regards,

Michael

[#3]

Hi Michael,

Thanks for your response.

I supplied lines from the log file and they show what the expression has evaluated to.

Here is the source of that particular stmt:      $(vNameOfMonth)     AS CalendarMonth,

The evaluate seems to work fine in other sections of the code.  I seem to recall that I was not able to use the sum without the Evaluate statement.

At the moment, I cannot test that as I cannot get past the "Field not Found" error.

John

[Anonymous]

Try to change it to
'$(vNameOfMonth)' AS CalendarMonth,

[#5]

Tried it - that had no effect on the error message.

I also moved the ProductGroupTurns line to the bottom of the load and that did not make a difference.

I also removed the "evaluate" and that did not make a difference.

John

1/20/2015 3:34:22 PM: 2084      TRACE ----------------- ProductGroupTurns FieldNumber: 4 - Inside INV Sub

1/20/2015 3:34:22 PM: 2084      ----------------- ProductGroupTurns FieldNumber: 4 - Inside INV Sub

1/20/2015 3:34:22 PM: 2087      UpdateOMInvHistory:

1/20/2015 3:34:22 PM: 2088        Load

1/20/2015 3:34:22 PM: 2089          WHGroup,

1/20/2015 3:34:22 PM: 2090          2015 AS FYear,

1/20/2015 3:34:22 PM: 2091          3 AS GlPeriod,  

1/20/2015 3:34:22 PM: 2092          '' AS PeriodType,

1/20/2015 3:34:22 PM: 2093          ''                  AS ValueType,

1/20/2015 3:34:22 PM: 2094          'Dec'  AS CalendarMonth,

1/20/2015 3:34:22 PM: 2095          201503      AS YM,

1/20/2015 3:34:22 PM: 2096          'Inventory' AS MetricType,

1/20/2015 3:34:22 PM: 2097          '' AS GoalTurns,

1/20/2015 3:34:22 PM: 2098          SUM(InvValPreviousMonth1) AS Value,

1/20/2015 3:34:22 PM: 2099          ProductGroupTurns  AS ProductGroup

1/20/2015 3:34:22 PM: 2100        Resident OMMetricsHistory

1/20/2015 3:34:22 PM: 2101        WHERE

1/20/2015 3:34:22 PM: 2102          ProductGroupTurns <> 'Not Categorized' AND

1/20/2015 3:34:22 PM: 2103          ProductGroupTurns <> 'MD-Coatings'

1/20/2015 3:34:22 PM: 2104        Group by ProductGroupTurns, WHGroup

1/20/2015 3:34:22 PM:          Error: Field not found - <ProductGroupTurns>

[Anonymous]

Looks correct to me...

I've seen situation when an error in a load/select statement was in fact due to an incorrect previous statement.  Take a close look at the script above that part.

[Colin-Albert · Accepted solution]

The test for the presence of the ProductGroupTerms field was on the InvValuationTable, but the resident load is from the OMMetricsHistory table and the error is saying that the field is missing from OMMetricsHistory.

You need to test if the ProductGroupTerms field exists in OMMetricsHistory.

[#8]

Duh !

Thanks Colin.

[Anonymous]

Indeed