[AL Static code analysis rule] No FlowFields on FlowFields

[DanielGoehler]

Description It would be nice, if the compiler would catch FlowFields on FlowFields.

Reason for the rule The compiler has all information to prevent that runtime error. Sometime you get an runtime error message

A flow field is part of the query column list, this is not supported

and the Page will be closed.

And sometimes the Web Client crashes. Event Log when this happens:

Server instance: BC
Tenant ID: default
Environment Name: 
Environment Type: Production
Session type: WebClient
Session ID: 197
User: 
Type: Microsoft.Dynamics.Nav.Types.Exceptions.NavSqlException
ErrorNumber: 207
TransactionRolledBack: False
SuppressMessage: False
ContainsPersonalOrRestrictedInformation: True
DiagnosticsSuppress: False
DiagnosticsMessage: Message not shown because the NavBaseException(string, Exception, bool) constructor was used.
MessageWithoutPrivateInformation: Message not shown because the NavBaseException(string, Exception, bool) constructor was used.
SuppressExceptionCreatedEvent: False
FatalityScope: None
ErrorLevel: Error
Message:
  The following SQL error was unexpected:
  Invalid column name 'Amount'.
  Statement(s) could not be prepared.
  SQL statement:
  SELECT SUM("21"."Amount") FROM "CRONUS".dbo."CRONUS AG$Cust_ Ledger Entry$437dbf0e-84ff-417a-965d-ed2bb9650972" "21"  WITH(READUNCOMMITTED)  JOIN "CRONUS".dbo."CRONUS AG$Cust_ Ledger Entry$dffa9ff0-1c9d-4b5a-9d45-008d30b9358a" "21_e3"  WITH(READUNCOMMITTED)  ON ("21"."Entry No_" = "21_e3"."Entry No_") WHERE ("21"."Open"=@0 AND "21_e3"."PRI-KD Down Pay_ Job No_"=@1) OPTION(OPTIMIZE FOR UNKNOWN)
  StackTrace:
     at System.Environment.GetStackTrace(Exception e, Boolean needFileInfo)
     at System.Environment.get_StackTrace()
     at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.ThrowNavSqlException(SqlExceptionAdapter exceptionAdapter, Boolean transactionNoLongerValid, String commandText, Boolean isLastExceptionARollbackCause, Boolean logExceptions)
     at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.<>c__DisplayClass129_0`1.<ExecuteFunction>b__0()
     at Microsoft.Dynamics.Nav.Types.NavThread.RunExternalAction[T](Func`1 action)
     at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.ExecuteFunction[T](Func`1 function, String commandText, Nullable`1 timeout, Boolean isAdoCommand)
     at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.ExecuteFunctionWithTrace[T](EventTask task, Func`1 function, String commandText, Int32 timeout, Boolean isAdoCommand)
     at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.ExecuteFunctionWithTrace[T](EventTask task, Func`1 function, NavSqlCommand command)
     at Microsoft.Dynamics.Nav.Runtime.NavSqlCommand.ExecuteReader()
     at Microsoft.Dynamics.Nav.Runtime.NavSqlAggregateCommand.ExecuteAggregate(FiltersAndMarks filter, NavDatabase database, Int32 companyIndex, Nullable`1 companyId)
     at Microsoft.Dynamics.Nav.Runtime.NavSqlAggregateCommand.<>c__DisplayClass14_0.<AggregateInternal>b__1(NavSqlCommand command)
     at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.ExecuteCommandAndCacheOnSuccess[T](NavSqlCommandCacheKey commandKey, Func`1 newCommandFunc, Func`2 executeFunc)
     at Microsoft.Dynamics.Nav.Runtime.NavSqlAggregateCommand.AggregateInternal(NavDatabase database, NCLMetaTable table, Int32 companyIndex, FiltersAndMarks filter, Boolean nonFlowFields, NCLMetaCalculationMethod aggregationMethod, FieldList aggregateFields, Boolean locking)
     at Microsoft.Dynamics.Nav.Runtime.SqlTableDataProvider.CalcNumeric(CalcNumericProviderRequest request)
     at Microsoft.Dynamics.Nav.Runtime.DataAccess.CalculateFields[T,U](T request, Func`2 createProviderRequest, Func`2 providerRequest)
     at Microsoft.Dynamics.Nav.Runtime.DataAccess.CalcNumeric(CalcNumericCacheRequest request)
     at Microsoft.Dynamics.Nav.Runtime.FlowFieldsHelper.CalcFieldsFromNonVirtualTables(NavSession session, Int32 companyToken, IRecordBuffer recordBuffer, FiltersAndMarks filtersAndMarks, SecurityFiltering flowFieldSecurityFiltering, NCLMetaField[] fieldsToCalc, Int32 recursionLevel)
     at Microsoft.Dynamics.Nav.Runtime.FlowFieldsHelper.CalcFields(NavSession session, Int32 companyToken, IRecordBuffer recordBuffer, FiltersAndMarks filtersAndMarks, NCLMetaField[] fieldsToCalc, Boolean onlyFieldsSourcedFromVirtualTables, SecurityFiltering flowFieldSecurityFiltering, Int32 recursionLevel)
     at Microsoft.Dynamics.Nav.Runtime.RecordImplementation.CalcFields(DataError errorLevel, NCLMetaField[] fields, Boolean onlyFieldsSourcedFromVirtualTables)
     at Microsoft.Dynamics.Nav.Runtime.RecordImplementation.CalcAutoCalcFields(Boolean calculateAll)
     at Microsoft.Dynamics.Nav.Runtime.RecordImplementation.InternalFindRecordWithoutCheckingValues(DataError errorLevel, PrimaryKeyCacheRequest request, Boolean useRecord, Boolean calcAutoCalcFields)
     at Microsoft.Dynamics.Nav.Runtime.RecordImplementation.GetRecord(DataError errorLevel, NavRecordId recordId, Boolean copyRecord, Boolean useFilters, Boolean calcAutoCalcFields)
     at Microsoft.Dynamics.Nav.Runtime.NavRecord.Get(DataError errorLevel, NavRecordId recordId, Boolean copyRecord, Boolean useFilters, Boolean calcAutoCalcFields)
     at Microsoft.Dynamics.Nav.Service.NSFormBatchOpen.HandleRegistrateDataAccess(FormOpenResponse openResponse, List`1 registeredForms)
     at Microsoft.Dynamics.Nav.Service.NSFormBatchOpen.Open(NavSession session)
     at Microsoft.Dynamics.Nav.Service.NSService.OpenForm(OpenFormRequest form)
     at SyncInvokeOpenForm(Object , Object[] , Object[] )
     at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
     at Microsoft.Dynamics.Nav.Service.ServiceOperationInvoker.RunInTransactionCombinator(ServiceOperation innerOperation, NSServiceBase serviceInstance, MethodBase syncMethod, Object[] inputs, Object[]& outputs)
     at Microsoft.Dynamics.Nav.Service.ServiceOperationInvoker.<>c__DisplayClass28_1.<Combine>b__1(NSServiceBase serviceInstance, Object[] inputs, Object[]& outputs)
     at Microsoft.Dynamics.Nav.Service.ServiceOperationInvoker.TransientErrorRetryCombinator(ServiceOperation innerOperation, NSServiceBase serviceInstance, MethodBase syncMethod, Object[] inputs, Object[]& outputs)
     at Microsoft.Dynamics.Nav.Service.ServiceOperationInvoker.<>c__DisplayClass28_1.<Combine>b__1(NSServiceBase serviceInstance, Object[] inputs, Object[]& outputs)
     at Microsoft.Dynamics.Nav.Service.ServiceOperationInvoker.ErrorMappingCombinator(ServiceOperation innerOperation, NSServiceBase serviceInstance, MethodBase syncMethod, Object[] inputs, Object[]& outputs)
     at Microsoft.Dynamics.Nav.Service.ServiceOperationInvoker.<>c__DisplayClass28_1.<Combine>b__1(NSServiceBase serviceInstance, Object[] inputs, Object[]& outputs)
     at Microsoft.Dynamics.Nav.Service.ServiceOperationInvoker.PushPopCombinator(ServiceOperation innerOperation, NSServiceBase serviceInstance, MethodBase syncMethod, Object[] inputs, Object[]& outputs)
     at Microsoft.Dynamics.Nav.Service.ServiceOperationInvoker.<>c__DisplayClass28_1.<Combine>b__1(NSServiceBase serviceInstance, Object[] inputs, Object[]& outputs)
     at Microsoft.Dynamics.Nav.Service.ServiceOperationTracer.TraceScopeCombinator(Category telemetryCategory, ServiceOperation innerOperation, NSServiceBase serviceInstance, MethodBase syncMethod, Object[] inputs, Object[]& outputs)
     at Microsoft.Dynamics.Nav.Service.ServiceOperationInvoker.<>c__DisplayClass28_1.<Combine>b__1(NSServiceBase serviceInstance, Object[] inputs, Object[]& outputs)
     at Microsoft.Dynamics.Nav.Service.ServiceOperationInvoker.<>c__DisplayClass10_0.<PerformanceCounterCombinator>b__0()
     at Microsoft.Dynamics.Nav.Service.ServiceOperationInvoker.PerformanceCounterCombinator(ServiceOperation innerOperation, NSServiceBase serviceInstance, MethodBase syncMethod, Object[] inputs, Object[]& outputs)
     at Microsoft.Dynamics.Nav.Service.ServiceOperationInvoker.<>c__DisplayClass28_1.<Combine>b__1(NSServiceBase serviceInstance, Object[] inputs, Object[]& outputs)
     at Microsoft.Dynamics.Nav.Service.ServiceOperationInvoker.InitClientTelemetryIdsCombinator(ServiceOperation innerOperation, NSServiceBase serviceInstance, MethodBase syncMethod, Object[] inputs, Object[]& outputs)
     at Microsoft.Dynamics.Nav.Service.ServiceOperationInvoker.<>c__DisplayClass28_1.<Combine>b__1(NSServiceBase serviceInstance, Object[] inputs, Object[]& outputs)
     at Microsoft.Dynamics.Nav.Service.ServiceOperationInvoker.TlsClearCombinator(ServiceOperation innerOperation, NSServiceBase serviceInstance, MethodBase syncMethod, Object[] inputs, Object[]& outputs)
     at Microsoft.Dynamics.Nav.Service.ServiceOperationInvoker.<>c__DisplayClass28_1.<Combine>b__1(NSServiceBase serviceInstance, Object[] inputs, Object[]& outputs)
     at Microsoft.Dynamics.Nav.Service.ServiceOperationInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
     at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)
     at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc)
     at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)
     at System.ServiceModel.Dispatcher.MessageRpc.Wrapper.Resume(Boolean& alreadyResumedNoLock)
     at System.ServiceModel.Dispatcher.ThreadBehavior.ResumeProcessing(IResumeMessageRpc resume)
     at Microsoft.Dynamics.Nav.Runtime.NavSynchronizationContext.<>c__DisplayClass1_0.<ClearThreadLocalStorageDelegate>b__0(Object state)
     at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
     at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
     at System.Threading.QueueUserWorkItemCallback.System.Threading.IThreadPoolWorkItem.ExecuteWorkItem()
     at System.Threading.ThreadPoolWorkQueue.Dispatch()
HResult: -2146233088

Bad code sample

tableextension 50100 Customer extends Customer
{
    fields
    {
        field(50100; "My Amount Field"; Decimal)
        {
            Caption = 'My Amount Field';
            Editable = false;
            FieldClass = FlowField;
            CalcFormula = sum ("Cust. Ledger Entry".Amount where("Customer No." = field("No."), Open = const(false)));
        }
    }
}
pageextension 50100 "Customer List" extends "Customer List"
{
    layout
    {
        addlast(Control1)
        {
            field("My Amount Field"; "My Amount Field")
            {
            }
        }
    }
}

Versions:

• AL Language: 5.0.335750
• Business Central: 16.5

[doality]

Any idea how to remove this code? I've commented out the flowfield and calctype to make to make it a regular field and it still throws this time out error visible only in the event viewer

[pri-kise]

@BazookaMusic it would really helpful if this warning (error) gets added, since this would avoid Runtime Errors, that potentially prevent a user opening some page where such FlowFields are added. This is still not controlled on compile time with latest AL Langauge Extension: v10.2.719883

[NKarolak]

@pri-kise atoader left Microsoft a long time ago.

[JMA1972]

Maybe MS should document first, in which cases flowfields on flowfields are allowed and in which cases they aren't. I'm using lookup flowfields on lookup flowfields with success for many years so it shouldn't be a general analysis rule.

[dzzzb]

Good point, at least (most?) lookup within lookup are OK.

MS should clearly document which are meant to work, which are not and why, and the latter subset should be compile-time errors - not run-time ones.