KNOWLEDGE BASE
Tech Tip: Using Tables and Field Numbers in 4D
PRODUCT: 4D | VERSION: | PLATFORM:
Published On: December 17, 1999

The commands Table and Field allow you to convert back and forth between table and field pointers and numbers. It can be dangerous to rely on these values on an ongoing basis in your database since they can change if your structure changes or you are forced to rebuild your database. Also, they can make your code less portable between databases. However, in certain circumstances they can provide elegant solutions to otherwise difficult programming challenges.

For example, let's say we want to compute the total sales for each quarter for customers that buy at least a million dollars worth of widgets from us (that's a lot of widgets):

Code example: 1
--------------------------

ARRAY LONGINT(LargeCustomerSales;4) ` an array to store our quarterly totals

QUERY([Customers]TotalSalesQ1>1000000) ` find the large customers
LargeCustomerSales{1}:=Sum([Customers]TotalSalesQ1) ` get the sum and store in array

` continue for the rest of the quarters
QUERY([Customers]TotalSalesQ2>1000000)
LargeCustomerSales{2}:=Sum([Customers]TotalSalesQ2)

QUERY([Customers]TotalSalesQ3>1000000)
LargeCustomerSales{3}:=Sum([Customers]TotalSalesQ3)

QUERY([Customers]TotalSalesQ4>1000000)
LargeCustomerSales{4}:=Sum([Customers]TotalSalesQ4)

We can use Table and Field numbers to make this job easier (especially if there were more than just the four fields used in this example):

Code example: 2
--------------------------

` This code assumes that the fields TotalSalesQ1...Q4 are in sequential order
` (one after the other 1,2,3,4 in the structure)

ARRAY LONGINT(LargeCustomerSales;4)
C_POINTER(\$tableptr)
C_POINTER(\$fieldptr)

\$fieldnum:=Field(->[Customers]TotalSalesQ1)
\$tablenum:=Table(->[Customers])

For (\$i;0;3)
\$tableptr:=Table(\$tablenum)
\$fieldptr:=Field(\$tablenum;(\$fieldnum+\$i))
QUERY(\$tableptr->;\$fieldptr->>=1000000)
LargeCustomerSales{\$i+1}:=Sum(\$fieldptr->)
End for

Note the use of pointers in the 4D calls QUERY and Sum. Placing the pointer symbol (->) after a pointer variable is called dereferencing and means "use the object that this pointer is pointing to".