Dependant Table Actions
Scenario: we are managing email templates for different clients.
Table A Contains - Email Type - a list of the types of email templates that exist
Table B Contains - Customers - a list of the customers
Table C Contains - EMail Templates - each record contains a template for each email type for each customer
I am trying to automate the management of table c based on actions that occur in table a and b. specifically
1) Bulk create a record in table c for every combination or records in tables a and b
2) If a record is created in table A, then create a record in Table c for every combination of records in a or b.
3) If a record is created in table B, then create a record in Table c for every combination of records in a or b.
4) If a record is deleted in table a, then remove the corresponding records from table c
5) if a record is deleted in table b, then remove the corresponding records from table c
I would really appreciate some help!
10 replies
-
Hi Andrew -
How are your TableA and TableB related?
That is a lot of things to do. Why don't we start with creating one record in Table C from Table A.
First create a button in Table A, call it whatever you want. We will take the following info as a given:
reference field name in TableC > fromTableA
Then in the button you would put a formula like this. Of course changing the names of the tables and fields to match your DB:
let curRec := this;
let xCreate := (create TableC);
xCreate.(fromTableA := curRec);
alert("New Record Create")
Now try doing the same from your TableB.
-
OK that's fine, i can do that
Table A containst the types of emails that can be configured for any client. Table B contains a list of the clients. And TableC contains the configuration. example
Table A values: welcome_email, not_active_reminder, new_invoice, invoice_due_reminder
Table B values: Acme Inc, Herrold Grace,
Table C contains a record for each of the above i.e.
Acme Inc | welcome_email, Acme Inc | not_active_reminder, Acme Inc | new_invoice, Acme Inc | invoice_due_reminder, Herrold Grace | welcome_email, Herrold Grace | not_active_reminder, Herrold Grace | new_invoice, Herrold Grace | invoice_due_reminder,
Every time we add a new client, or a new emaill_template i want to create the corresponding records in Table C. e.g.
If I add client "Goodhew Exports" the following records would be created:
Goodhew Exports | welcome_email, Goodhew Exports | not_active_reminder, Goodhew Exports | new_invoice, Goodhew Exports | invoice_due_reminder,
And if I added email_template "invoice_past_due" the following records would be created
Acme Inc | invoice_past_due, Herrold Grace | invoice_past_due, Goodhew Exports | invoice_past_due
I hope that makes sense?
-
I assume ther eis a for loop that I need to incorporate referencing the second table?
let curRec := this;
let xTableB := TableB;
for xTableB do
let xCreate := (create TableC);
xCreate.(fromTableA := curRec);
alert("New Record Create")
-
Ok so ive figured out the adding of the records with:
let curRec := this;
for xemail_type in select email_type do
let xemail_template := (create email_template);
xemail_template.(Portfolio := xemail_type);
xemail_template.(client := curRec)
endand likewise for the other table.
Now I need to understand how to delete the records. So if I delete a record from Table A or B, to delete the corresponding records from Table C
ANY IDEAS?
-
Hi Andrew
Let's leave No 1 to last
2. Create a record in TableA, put this in the Trigger on create
let curRec := this;
for xTableB in select TableB do
let xCreate := (create TableC);
xCreate.(fromTableA := curRec);
xCreate.(fromTableB := xTableB)
end
3. Create a record in TableB, put this in the Trigger on create
let curRec := this;
for xTableA in select TableA do
let xCreate := (create TableC);
xCreate.(fromTableB := curRec);
xCreate.(fromTableA := xTableA)
end
4. if your table reference to TableC is called toTableC then create a Delete button in TableA with the following code
delete toTableC;
delete this
5. For the relationship between TableC and TableB, either set Composition to Yes. This takes care of cascade deletes in TableC on deletion of a record in TableB or create a Delete button with the same code as 4. ( If the table reference to TableC is called toTableC).
1. If there are no records in TableC then you can use this code
for xTableA in select TableA do
for xTableB in select TableB do
let xCreate := (create TableC);
xCreate.(fromTableA :=xTableA);
xCreate.(fromTableB := xTableB)
end
end
Regards John
-
Thanks John - BIG HELP
-
So I have the query as advised - which is working great
let curRec := this;
for xTableA in select TableA do
let xCreate := (create TableC);
xCreate.(fromTableB := curRec);
xCreate.(fromTableA := xTableA)
end
But I want to add an exception. I have another table D. Table D stores a value of "Active" or "Not Active". Each Record in Table A is linked to a record in Table D.
I want to only create a record in Table A if the Table A record is "Active"
Any help would be appreciated
-
Hi Andrew
If your relationship field is called fromTableD and the Active / Not Active fleld in TableD is called Status then you can use
let curRec := this;
for xTableA in select TableA where fromTableD.Status = "Active" do
let xCreate := (create TableC);
xCreate.(fromTableB := curRec);
xCreate.(fromTableA := xTableA)
end
-
SSD chemical solution for sale in South Africa +27735257866 Zambia,Zimbabwe,Botswana,Lesotho,Swaziland,Somalia,Namibia,Qatar,Egypt,UAE,USA,UK,Turkey,Hong Kong,DRC,China,Ethiopia,Eritrea,Sudan,Morocco,Brunei,Japan,Algeria,Thailand,Tunisia,Kenya,Spain,Italy,Tanzania,Bahamas,Taiwan,Indonesia,Singapore,Czech Republic,Serbia,Palau,Lithuania,Malaysia,Myanmar,Kuwait,Fiji,Jordan,Yemen,Bahrain,Philippines,Israel... We clean all types of Currencies CALL Agent Mbuga +27735257866 OR Email sheikhmbuga5@gmail.com http://mbugassdsolution.website2.me https://paparazaq.blogspot.com http://bestssdchemicalsolution.over-blog.com SSD solution chemical for cleaning black money notes, SSD solution used to clean all type of blackened, tainted and defaced bank notes. We sell ssd chemical solution used to clean all type of black money and any color currency, stain and defaced bank notes with any other equipment being bad. Our technicians are highly qualified and are always ready to handle the cleaning perfectly. Our chemical solution is 100% pure.
-
Automatic Ssd Solution And Activation Powder for sale in South Africa +27735257866 Zambia,Zimbabwe,Botswana,Lesotho,Swaziland,Somalia,Namibia,Qatar,Egypt,UAE,USA,UK,Turkey,Hong Kong,DRC,China,Ethiopia,Eritrea,Sudan,Morocco,Brunei,Japan,Algeria,Thailand,Tunisia,Kenya,Spain,Italy,Tanzania,Bahamas,Taiwan,Indonesia,Singapore,Czech Republic,Serbia,Palau,Lithuania,Malaysia,Myanmar,Kuwait,Fiji,Jordan,Yemen,Bahrain,Philippines,Israel... We are specialized in chemistry for anti-breeze bank notes. We also do chemicals melting and recovering of all type of bad money from black to white. We also sale chemicals like tourmaline, S.S.D. Chemical solution, castro x oxide, A4, And many other activation powder. About SSD solution for cleaning black money chemical and allied product incorporated is a major manufacturer of industrial and pharmaceutical products with key specialization in the production of ssd automatic solution used in the cleaning of black money and defaced money and stained bank notes with anti -breeze quality. The SSD solution in its full range is the best chemical in the market for cleaning anti breeze bank notes, defaced currency, and marked notes. Others for damaged notes, bills. Please call Agent Mbuga +27735257866 OR Email sheikhmbuga5@gmail.com http://mbugassdsolution.website2.me https://paparazaq.blogspot.com http://bestssdchemicalsolution.over-blog.com
Content aside
- 3 yrs agoLast active
- 10Replies
- 800Views