0

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

null
    • Fred
    • 3 yrs ago
    • Reported - view

    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.

    • Andrew.5
    • 3 yrs ago
    • Reported - view

    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?

    • Andrew.5
    • 3 yrs ago
    • Reported - view

    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")

    • Andrew.5
    • 3 yrs ago
    • Reported - view

    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)
    end

     

    and 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?

    • John_Halls
    • 3 yrs ago
    • Reported - view

    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

    • Andrew.5
    • 3 yrs ago
    • Reported - view

    Thanks John - BIG HELP

    • Andrew.5
    • 3 yrs ago
    • Reported - view

    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

    • John_Halls
    • 3 yrs ago
    • Reported - view

    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

    • DELETEDsheikhmbuga5
    • 3 yrs ago
    • Reported - view

    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.

    • DELETEDsheikhmbuga5
    • 3 yrs ago
    • Reported - view

    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

Login to reply

Content aside

  • 3 yrs agoLast active
  • 10Replies
  • 800Views