Upsert plugin with WHERE condition

[AlexTCGPro]

Hey guys.
I'm very new to fabrik and I'm in need of a little guidance.

I need to record payments.
I created two tables and two forms, one for debts and the other for payments.

The debt table is like this:
ID, year, description and debt

I'm now managing the payment form, which is like this:
Manually type ID, then a cascading dropdown of the years for that ID appears and a cascading dropdown of all the descriptions that person has in that specific year turns up and finally setting those three values would make the debt appear. Then a final field where one can type how much that person is paying at the current moment.

I'm playing with the upsert plugin to update the debt value in the debt form according to what the person is paying, meaning if the person pays full then that value would update to 0, if is paying just a little then I want that value to update to how much debt remains.

For this I need the upsert plugin to update the debt value but only if ID, year and description match for that specific value, just one of those values matching won't do. I suppose that in order to do this I need to set some php code in the condition field to tell the plugin that I want to update the 'debt' field according to the foreign key I have assigned (ID) but only WHERE year and description also match with the fields in the form.

I have found very little documentation about this plugin, what it is on the wiki doesn't even correspond with how the plugin looks now. I'm sure this is something really dumb, I would really appreciate any help.

 

[troester]

If you have determined your dept record (by selecting (customer?) ID and year and description) you should have the record id (primary key) of this record which is used in the upsert plugin.

 

[AlexTCGPro]

Thanks for answering, ID corresponds to customer ID, so is not unique per debt, I do have a primary key which corresponds to each different debt, how would I go to make the upsert plugin update only the specific one, do I need to set any conditional code?

 

[cheesegrits]

Hmm, nope, the "Condition" just tells the plugin whether to run or not.

The way the upsert plugin works is that you have to have a one-to-one relationship between the two tables ... either an FK (foreign key) on your form's table pointing to a single row PK (primary key) value in the table you are upserting, or vice versa ... an FK on the upsert table, pointing back to the PK of the row on your form you are submitting.

So in this case, you'd either need payment.debt_id pointing to debt.id, or debt.payment_id pointing to payment.id.

If that isn't the case, you'll have to roll your own code in a generic PHP form plugin running 'onAfterProcess' on the payment form, (so it runs after the payment has succesfully written to the database) which looks at the payment form data, and figures out which debt it needs to modify (or insert).

-- hugh

 

[AlexTCGPro]

I see, thank you, I think I can figure something with the primary key of the debt table like you guys said, doesn't seem as difficult as I thought

 

[cheesegrits]

Typically you'd have a join element on your form which points to the table you want to upsert, and specify that as the FK.

-- hugh