multi-table get, update, insert.

Hi All,
I figured this out with CF3, but CF4 is so different, I need a little help...
Tables
- - - registration
- - - - - fields in existing record (needs an update)
- - - - - - - -cf_id = autoincrement
- - - - - - - -cf_user_id = users.id
- - - - - - - -other fields = some populated some not.
- - - main
- - - - - fields in existing record (needs an update)
- - - - - - - -rm_id = autoincrement
- - - - - - - -id_link = null - to be updated to = registration.cf_id
- - - - - - - -rm_card = 999999999999
- - - - - - - -other fields = some populated some not.
- - - log = transactional record
- - - - - fields in EMPTY record (needs an insert)
- - - - - - - -log_num = autoincrement
- - - - - - - -log_rm_card = to equal main.rm_card
- - - - - - - -log_rm_id_link = to equal main.rm_id
- - - - - - - -other fields = to be populated with some field data from both the "Registration" and "Main" tables

My goal is to allow the user to Link the existing record in the Main table to the registered user by posting the registration.cf_id into the main.id_link field (an update). And create a log record of that linking in the Log table (an insert).

A logged in user goes to a form I made called "Link Number", here it is (In text form)...

Link # to My Account.
Card Number XXXXXXXXXXXXXXXXXX
Submit

So the user inputs the number and submits, if it matches a record in the Main (form input card number = main.rm_card) AND that record does not already have a value in main.id_link (main.id_link = NULL)then it updates the main.id_link with the registration.cf_id else it gives an error.
Also if it is successful in updating the main.id_link it must also insert a record into the log table setting the log.log_rm_card = main.rm_card and log.log_rm_id_link = main.rm_id, else not.

Ive got some of it kinda working but mostly not..

A quick and dirty outline of the event flow would be very helpful.

My event flow currently looks like this..
On Load Custom Code - get_uid (3)
- -DB Record Loader (12)
- - On Record Found
- - - DB Record Loader (14)
- - - - On Record Found
- - - - On Empty Result
- - - - On No/Empty Param Passed
- - On Empty Result
- - On No/Empty Param Passed
- -Style Form (8)
- -Show html (0)
On Submit
- -Custom Code - extra_fields (20)
- -DB Save (16)
- -DB Save (17)
- -DB Save (18)

I have several debugs
and this is what I see upon loading the form...
Data Array:

Array
(
[Itemid] => 128
[option] => com_chronoforms
[view] => form
[id_link] => 72
[cf_id] => 72
[status] => a
[anniv_date] => 2012-08-01 - 13:34:26
[exp_date] => 2013-08-01
[rex_dlu] => 2012-08-01 - 13:34:26
[tran_oper] => USER
[tran_store] => WEB
[tran_comment] => Card Activation - Link
)

Validation Errors:

Array
(
)
But the data is NOT doing the updates, instead it is trying to insert another record to the registration table.

Please help...

I'm sure it has to do with the below....

I don't understand in the "database" activities the use of
[list]

DB Field - The field name which will be used to query the table record. huh???? is this supposed to be the key? OR a key? How can a field name be used to query a record?, Where in an SQL statement would this be found?

Request Param
The param name - which will exist in the request url to the form, its value will be used to load the target db record, if the value of this parameter is an array then the array values will be used inside 'IN' statement. I guess like, cf_id = 86, but where does the 86 (the value) come from? Is it a field name? Is it a data value like <?php echo $form->data['my_value'] ?> ???

Model ID - The key under which the loaded record data will be stored in the form->data array. huh? I thought that was already in $form->data['my_value']???? Then what is this for?

Load Under Model ID - yes/no - I would like to know what the Model ID value would bring to the table, there is no such thing in a form or an SQL statement, so what is this for?

[/list]

Hi the_fitz,

I'm not following this correctly :-(

What do the DB Record Loaders in the On Load event do? It seems to me that you don't have anything to look up until after the form is submitted.

I'd be inclined not to use the On Record Found event unless you absolutely need to. In this case you are probably better using two linked DB Record Loaders to make a inquiry on both tables at once.

Bob

Hi Bob and thanks for the response. I know, I'm not even following this correctly, because I am confused.

Is there an explanation of how to "using two linked DB Record Loaders to make a inquiry on both tables at once"?

So succinctly..
load the users record from the REGISTRATION table - to get the REGISTRATION.CF_ID using WHERE REGISTRATION.CF_USER_ID = <?php echo $user->id ?>
Display the form and allow the user to enter the CARD_NUMBER and then Submit
Find the CARD_NUMBER in the MAIN table..
- -if the record found with the MAIN.CARD_NUMBER (a key but not primary) continue
- -ELSE if not found then error message "Number does not exist" STOP PROCESSING (Show Stopper)
- -then check to see if the MAIN.ID_LINK IS NULL Continue
- -ELSE give error - "Number already assigned" STOP PROCESSING (Show Stopper)
- -Then UPDATE The MAIN table with MAIN.ID_LINK = REGISTRATION.CF_ID and update a couple of other fields.
- -AND INSERT a transaction record into the LOG table with some info from each of the other records (REGISTRATION and MAIN records) and some fixed information.
DONE
Field name juggling for the 3 different tables requires assigning values like LOG.TRAN_STORE = REGISTRATION.ORIG_STORE, etc ??? Not so sure about this but I seem to get some results from "customcode"
Sounds easy, but I have been working on this for about 15 hours and can't quite get it... :?

Thanks again....
MrFitz

Hi the_fitz,

I'm not following this correctly :-(

What do the DB Record Loaders in the On Load event do? It seems to me that you don't have anything to look up until after the form is submitted.

I'd be inclined not to use the On Record Found event unless you absolutely need to. In this case you are probably better using two linked DB Record Loaders to make a inquiry on both tables at once.

Bob

Ok,Ok
Another few hours and it finally worked...

Thanks to all!!!!😀

MrFitz

Hi the-fitz,

Well done.

Bob