Filemaker ExecuteSQL in a table view field

There is a need to verify the quantity of part number in the main table against the total quantity in another table. It is impossible to use summary type field as it requires break field but summary type can only provide running total.

In the table view on a layout the subtotal can be computed using the sub-summary feature but it cannot be use as a link to a related table.

The only other option is to use ExecuteSQL. The fist thought is to use the function directly as a calculation. The syntax of the function is as follows

ExecuteSQL("SELECT SUM(Quantity) FROM FirstTable Where PartNumber =?";"";"";PartNo)

The result is "?". According to documentation, it is the result returned when the ExecuteSQL encounters SQL error. However, there is absolutely no error in the SQL statement. It turned out that another function "Let" must be used in conjunction with ExecuteSQL. The modified calculation is as below

Let($query=ExecuteSQL("SELECT SUM(Quantity) FROM FirstTable Where PartNumber =?";"";"";PartNo);$query)

Unfortunately, it failed again. In the field options, there is a setting to not store the result in the table. It must be set so that the calculation must be done every time the field is shown. Only then the result of the calculation can be shown.

The next step is to link this result with another table together with the part number. It again failed. The reason is due to the previous paragraph setting.

There is no way the calculated field can be used as a link to a related table. The only way is to run a script to add the result into a text field then use that text field to link to the related table. It cannot be used for dynamic update but it worked.

One worry is the fact that ExecuteSQL runs a query for every field that is shown. This means that there will be considerable delay since it cannot be stored. If there are millions of records to be updated this  way, it will take forever.

The conclusion is that it can be done but not real time and it causes considerable loading to the process. How I wish FM add a summary function to show total with breaking field instead of running total.