OP Many to one join

version 1.5

OP Many to one join (connectionID; manyTableID; oneTableID) Longint

Parameter	Type		Description
connectionID	Longint		Connection ID with target server
manyTableID	Longint		Number of the Many file in the database
oneTableID	Longint		Number of the One file in the database
Function result	Longint		Error code result for the function

OP Many to one join creates a new selection of records in oneTableID based on the selection of records in manyTableID. For example, if you have a selection of records in the many table, you can retrieve the records in the One table that are related to that selection.

This command can work across several levels of relations. For example, according to the [Customers]/ [Invoices] table structure, a call to OP Many to one join with the Many file [Invoices] and the One file [Customers] selects the customers related to the selected records in [Invoices].

OP Many to one join works with both manual and automatic relations.

If OP Many to one join executes successfully, it returns 0. Otherwise, this function returns one of the following errors:

Error Code	Description
-9972	Table number is out of range.
10128	The 4D Open for 4th Dimension package has not been initialized.
10136	The connection does not exist.
10154	This command cannot be executed right now.

This example prints a list of the customers who received the 10 largest invoices for the current year:

   C_LONGINT(vRecords; $ErrCode)
   C_STRING (10;vValue)
   C_LONGINT(vTableInvoices;vTableCustomers;vFieldDate;vFieldAmount;vFieldRef)

   $errCode:=OP Get one field number (vConnectID;"[Invoices]Invoice date";vTableInvoices;vFieldDate)   
   $errCode:=OP Get one field number (vConnectID;"[Invoices]Invoice amount";vTableInvoices;vFieldAmount)
   $errCode:=OP Get one field number (vConnectID;"[Customers]Ref";vTableCustomers;vFieldRef)
   
      `Compute this year's 1st of january
   vValue:="01/01/" + String ( Year of ( Current date ) ;"####") 
 
      ` Query for this year's invoices
   $errCode:=OP Single query (vConnectID;vTableInvoices;vFieldDate;">";->vValue;vRecords)

      `Order by decreasing amount (largest amount is first of selection)
   $errCode:=OP Single order by (vConnectID;vTableInvoices;vFieldAmount;"<") 
   
      ` Focus on the 10 largest invoices
   $errCode:=OP Reduce selection (vConnectID;vTableInvoices;10)

      ` Create a selection of customers for those invoices
   $errCode:=OP Many to one join (vConnectID;vTableInvoices;vTableCustomers)

      ` Find out how many customers in the new selection
   $errCode := OP Records in selection (vConnectID;vTableCustomers;vRecords)
   ALERT (" This year's top ten invoice correspond to " + String (vRecords) + " customers")

   PrintCustomers

OP One to many join, OP Records in selection, RELATE ONE SELECTION.