Trigger an Update to two tables upon updating a combobox field

Scriptcase 8 Discussion
#1

I have the following tables:

client {id_cliente(pk)}
collection {id_collection(pk),id_cliente(fk)}
invoice {id_invoice(pk),id_collection(fk)}

a collection, is 1 or more clients
an invoice, has only one colection_id
a client, can belong to 0 1 or more collections

When I am in the form, I am selecting several CLIENTS from a combobox, WHICH belong to the table CLIENT

when that happens, and I do select AT LEAST 1 CLIENT, the following should happen;

  1. create a NEW RECORD in table COLLECTION with a unique ID_COLLECTION and the ID_CLIENT which has been selected before

when I select more than 1 client, the following shoud happen

  1. create as many NEW RECORDS in table COLLECTION as ID_CLIENT’s I have selected previously

EXAMPLE:

selection of [1,2,3] clients [john,peter,martin]

-> this should trigger

creation of 3 records in table COLLECTION with the same ID_COLLECTION but with each one containing a unique ID_CLIENT

that way I know that in that invoice, there are 3 different clients and produce 3 copies of each invoice

KIND REGARDS,
JSD

#2

Unless you need the collection table for something else, a many to many relation just needs a dedicated table with the keys of id_invoice and id_client and no need for new/extra keys in the invoices and clients tables.
So it should be:

client {id_client(pk)}
invoice_client {id_invoice(pk),id_client(pk)}
invoice {id_invoice(pk)}

The invoice_client table will have all the info: you can query it for all invoices associated to a client or for all clients associated to an invoice.

Also, SC can handle many to many relations in forms by using a specific control and settings:

http://www.scriptcase.net/docs/en_us…-relationships

#3

WONDERFUL ANSWER!
but I will keep this updated shortly as I am going to try and implement that logic…

Let me though ask you the following…

PROBLEM

when I am in a FORM, lets say INVOICE, I have subblocks
then in order to handle the invoice, I create a FIELD named, CLIENT which I put at the top of the form, BUT it belongs to the table CLIENT, not INVOICE

How can I trigger an update on INVOICE, from that UPDATE on the field CLIENT?
I do not seem to find a TRIGGER function for those SINGLE added FIELDS which query other tables rather than that in a FORM that includes a specific unique TABLE

kind regards!
JSD

#4

I’m not sure I’m following you, but if you’re saying you’ll convert to the standard way to set up your many to many relation (as I described in my previous post),
on ScriptCase you could try and use the link in my previous post.
Once setup, SC will update the relation table (invoice_client) automatically.

If the ScripCase way of handling many-many relations on forms won’t meet your requirements,
I think you could do this:

  • create a multi record form for the invoice_client table.
  • in the invoice form, use the invoice_client form you just created as details, linking it to the id_invoice field
  • in the invoice_client form: [LIST]
  • hide the id_invoice field
  • convert the id_client field to type select and make it query the clients table; this is to retrieve the field(s) you want to show instead of the id_client
[/LIST] In this way you should be able to add as many rows with clients as you need. Basically it's the same logic used when adding rows of invoiced items to an invoice.

As for the specific problem:
on the various form events related to inserts or updates, you can perform SQL actions to write from any source (added fields\variables\whatever) to any external table(s). Note that if you handle it in this way, you should also write some code to read from the external table(s) in the form onLoad.

  • edit: completely rewritten -
#5

Thank you so much!
I will put this into practice as soon as I can today
will write back about the outcomes

Regards
JSD