Autogenerate with fieldvalue appears to miss rows (Forward Cover)

[nsm1234567]

Hi There,

I've been battling with an issue that I'm hoping someone can assist with.  I'm trying to create a "Forward Cover" calculation.  The idea is to work out how many weeks of stock I have on hand from a given point based on forecasted sales.  I've attached the app I've put together to do this and the calculation for the specific entry is in the spreadsheet.  From the spreadsheet you'll see that we cross into negative values after 59 weeks.  However, the app calculates 54 weeks.  The reason for this as far as I can tell is that zero-value rows aren't being added to the table when I do the autogenerate.

So in the spreadsheet on rows 82 and 83 there are two entries of 1356000.75 whereas the app only has one entry.  Can anyone see what I'm missing in my code that's preventing the zero entries from being added to TempTable?  Been struggling on it for a while and I'm not really getting anywhere.

Message was edited by: Nathan S Added the wrong excel spreadsheet!

[sunny_talwar · Accepted solution]

I have not deep dived into your code, but I know this for fact that FieldValue() function only looks at distinct value from a field. So if something is repeating, it will only be captured once. Could that be the reason for this?

So in the spreadsheet on rows 82 and 83 there are two entries of 1356000.75 whereas the app only has one entry.

[swuehl]

I think Sunny is right.

FieldValue() is operating on the symbol table, where the distinct field values are stored.

I think you should use inter record functions like Peek() to operate on the resident data table rows.

[nsm1234567]

Thanks so much.  That was the issue.  Did a peek on the table and now I'm getting the results I'm expecting

[sunny_talwar]

Superb, I am glad it all worked out in the end.

Best,

Sunny