2

When synchronizing my cash flow model, I get the following error:

De feiten kunnen niet in Excel cellen geplaatst worden door fouten in de  formules. Bijvoorbeeld de expressie '=SUMIF(BALTable[ID], "=" & [ID], BALTable[Close])' veroorzaakt een fout in cel AP!G7. 
Corrigeer deze fout eerst en probeer het opnieuw. 
U moet de Engelse versie gebruiken van Excel formules zoals 'SUM' in plaats van het Nederlandse 'SOM'.
Bovendien moet u een komma ',' gebruiken als scheider voor functieparameters in plaats van de puntkomma ';'.

Translated to English:

The facts can not be put into Excel cells due to errors in the formulas. 
For instance, the expression '=SUMIF(BALTable[ID], "=" & [ID], BALTable[Close])' causes an error at cell AP!G7. 
Please fix this error first and try again. 
You must use the English versions of Excel formulas such as 'SUM' instead of the Dutch 'SOM'. 
Also, you must use a comma ',' as separator for function arguments instead of a semicolon ';'.

Full error stack:

Type: System.Runtime.InteropServices.COMException   
bij System.Dynamic.ComRuntimeHelpers.CheckThrowException(Int32 hresult, ExcepInfo& excepInfo, UInt32 argErr, String message)
bij CallSite.Target(Closure , CallSite , ComObject , Object )
bij CallSite.Target(Closure , CallSite , Object , Object )
bij Invantive.Producer.Control.Utility.SetExcelRangeValues(ModelCache modelCache, Workbook workbook, Range range, Object[,] values) in File169:regel 5290Type: Invantive.Configuration.ValidationException
bij Invantive.Configuration.ValidationException..ctor(String errorCode, String errorMessage, String kindRequest, String localStackTrace, String nk, Exception innerException)
bij Invantive.Producer.Control.Utility.SetExcelRangeValues(ModelCache modelCache, Workbook workbook, Range range, Object[,] values)
bij Invantive.Producer.Control.SyncToDatabaseForm.SyncDownloadRows(SystemWorkingContext context, iea_blocks_v block, Int32 numberOfRows, Int32 fakeRowCount, Int32 numberOfColumns, ResultSet resultTable, Decimal step, Int32 currentBlock, Int32 idColumnNumber, Int32 transactionColumnNumber, Point3d dataStartPoint, Dictionary`2 additionalSelectFields, Dictionary`2 fieldsByNameDictionary, Dictionary`2 columnBeginAndEndPoints, Dictionary`2 blocksByCodeDictionary, List`1 fields, Dictionary`2 blockDimensions, ConcurrentBag`1 expressionFieldForCheckPendingChange, Cube innerBorder)
bij Invantive.Producer.Control.SyncToDatabaseForm.SyncDownload(DoWorkEventArgs e)
bij Invantive.Producer.Control.SyncToDatabaseForm.syncBackGroundWorker_DoWork(Object sender, DoWorkEventArgs e)
bij System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
bij System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)
bij System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Object[]& outArgs)
bij System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)
bij System.Runtime.Remoting.Proxies.AgileAsyncWorkerItem.ThreadPoolCallBack(Object o)
bij System.Threading.QueueUserWorkItemCallback.WaitCallback_Context(Object state)
bij System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
bij System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
bij System.Threading.QueueUserWorkItemCallback.System.Threading.IThreadPoolWorkItem.ExecuteWorkItem()
bij System.Threading.ThreadPoolWorkQueue.Dispatch()
bij System.Threading._ThreadPoolWaitCallback.PerformWaitCallback()
bij Invantive.Producer.Control.Utility.SetExcelRangeValues(ModelCache modelCache, Workbook workbook, Range range, Object[,] values) in File169:regel 5352
bij Invantive.Producer.Control.SyncToDatabaseForm.SyncDownloadRows(SystemWorkingContext context, iea_blocks_v block, Int32 numberOfRows, Int32 fakeRowCount, Int32 numberOfColumns, ResultSet resultTable, Decimal step, Int32 currentBlock, Int32 idColumnNumber, Int32 transactionColumnNumber, Point3d dataStartPoint, Dictionary`2 additionalSelectFields, Dictionary`2 fieldsByNameDictionary, Dictionary`2 columnBeginAndEndPoints, Dictionary`2 blocksByCodeDictionary, List`1 fields, Dictionary`2 blockDimensions, ConcurrentBag`1 expressionFieldForCheckPendingChange, Cube innerBorder) in File170:regel 3466
bij Invantive.Producer.Control.SyncToDatabaseForm.SyncDownload(DoWorkEventArgs e) in File170:regel 2332

How to fix this?

Guido Leenders
  • 780
Daniëlle Snoeijer
  • 23

1 Answers1

1

As you can read from the error message, it tries to create a formula which is 'invalid'. In this case it probably means that the referenced table or their columns don't exist. From the naming, I guess you are using one of our demo models (I work for the company providing the software).

Cause

The thing that most likely happened is that you created a new sheet somewhere between existing sheets. As you can see from the model editor, the name of the block does not correspond to the name of the sheet:

enter image description here

That caused the formula to fail, since on AP there is no table with the column ID in it.

How to solve

  1. The most important rule is Always insert new sheets at the end of the model. If you insert a sheet, test if the model still works by synchronizing it.

  2. If you really need (or want) to insert a sheet in the middle, first empty the model by publishing to a new model (this resets all layout and data), and then add the sheet. After the first time you synchronize, you have to go over this step again.

I will see if we can make some checks to prevent users from breaking the model by accidentally inserting a sheet.

Community
  • 1
Patrick Hofman
  • 594