OP One to many join

version 1.5

OP One to many join (connectionID; oneTableID; manyFieldID) Longint

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

OP One to many join creates a new selection of records in manyTableID based on the selection in the one file.

Based on the example for OP Many to one join example, in the [Customers]/[Invoices]table structure, a call to OP One to many join for [Invoice]Customer ID will select the invoices in the [Invoices] table that refer to the customers in the [Customers] table current selection.

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

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

Error Code	Description
-9951	Field is not related to another one.
-9971	Field number is out of range.
-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 finds the list of all last year's invoices received by the customers who got the top 10 invoices of the current year. In other words, if the largest invoice of this year is to ACME Inc., then we want the list of all the invoices to ACME Inc. for last year.

   C_LONGINT (vRecords; $ErrCode)
   C_STRING (10;vValue1;vValue2;vValue3)
   C_LONGINT (vTableInvoices;vTableCustomers;$unused1)
   C_LONGINT (vFieldDate;vFieldAmount;vFieldCustID;$unused2)

      ` Get [Invoices] tableID and [Invoices]Invoice date fieldID
   $errCode:=OP Get one field number (vConnectID;"[Invoices]Invoice date";vTableInvoices;vFieldDate)
      ` Get [Invoices]Invoice amount fieldID   
   $errCode:=OP Get one field number (vConnectID;"[Invoices]Invoice amount";$unused1;vFieldAmount)
      ` Get [Invoices]Customer ID fieldID   
   $errCode:=OP Get one field number (vConnectID;"[Invoices]Customer ID";$unused1;vFieldCustID)
      ` Get [Customers] tableID
   $errCode:=OP Get one field number (vConnectID;"[Customers]Ref";vTableCustomers;$unused2)
   
      `Compute last year's 1st of January
   vValue1:="01/01/" + String ( Year of ( Current date )-1 ;"####") 
      `Compute last year's 31st of December
   vValue2:="12/31/" + String ( Year of ( Current date )-1 ;"####") 
      `Compute this year's 1st of January
   vValue3:="01/01/" + String ( Year of ( Current date ) ;"####") 

      ` Query for invoices dated after January 1st last year
   $errCode:=OP Single query (vConnectID;vTableInvoices;vFieldDate;">=";->vValue1;vRecords)
      ` Query in the resulting selection fo invoices dated before December 31st last year
   $errCode:=OP Single query selection(vConnectID;vTableInvoices;vFieldDate;"<=";->vValue2;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)

      ` From that selection of customers, create the selection of all their invoices
   $errCode:=OP One to many join (vConnectID;vTableInvoices;vFieldCustID)

      ` Query the resulting selection for invoices dated afterJanuary 1st this year
   $errCode:=OP Single query selection(vConnectID;vTableInvoices;vFieldDate;">=";->vValue3;vRecords)

   PrintInvoices

OP Many to one join, OP Single query, RELATE MANY SELECTION.