My problem is that I don't know how to reference the subform Primary Key. In the meanwhile, I'm using a user entered semi-unique value RawMICR, but it's not as good as the Primary Key CheckID
Here's the code:
Code: Select all
foreach(nuSubformArray('Checks_subform', false) as $i=>$row){
$p.=$nuHash[$row.'NewCustomerFirst']."\n";
if($nuHash[$row.'CustomerID'] == '' && $nuHash[$row.'NewCustomerLast'] != ''){
if($nuHash[$row.'RawMICR'] != '') {
$sql1 = "INSERT INTO Customers (CustomerID, cus_number, cus_type, CustomerFirst, CustomerLast,CustomerCountry, ForeignCountry, Active, OFACVerified) VALUES
(?, ?, ?, ?, ?, 'ISRAEL', 'USA', '1', '0')";
$CustomerID = nuID();
$q1 = nuRunQuery($sql1, array($CustomerID, nuNextNumber('Customer_number'), ($nuHash[$row.'NewCustomerFirst']=='' ? 'Company' : 'Individual'), $nuHash[$row.'NewCustomerFirst'] ,$nuHash[$row.'NewCustomerLast'] ), true);
$sql2 = "UPDATE Checks SET CustomerID = ? WHERE RawMICR = ? AND TransID = ?";
$search = array ($CustomerID, $nuHash[$row.'RawMICR'],'#RECORD_ID#');
$r=nuRunQuery($sql2, $search);
$p.=print_r($r,true).print_r($search,true);
}
}
}
1. Is this the right approach?
2. How can I get the subform Primary Key?
3. I tried using the $q1 as returned by using isInsert parameter of nuRunQuery, but it returned 0. Is this an issue with the code, or my setup? Customers does not have its own auto_increment, but uses Customer_number table as advised.
Thanks!