0

Trigger - use data in table 1 to create line in table 2

Hello,

I am struggling to create what I think will be a trigger event...?

I have two tables - Components and Purchase Order.

What I am trying to achieve is the following:

1. When field 'Purchase Order' is switched to Yes in Components, a line gets created in the Purchase Order table.

2. This line should use the text from the 'Specification' field in Components, and enter it into the 'Item Description' field of Purchase Order. It should also take the cost from 'RRP' in Components, and enter it into 'RRP' in Purchase Order.

3. Ideally, if the field 'Purchase Order' is unticked, the corresponding line would also be removed from the Purchase Order table.

 

Please can someone help me with the code to do this, I just can't seem to get it to work. Is it even possible?

5 replies

null
    • Choices_Software_Dean
    • 4 yrs ago
    • Reported - view

    1. in Purchase Order table go into Edit Fields and click "Create table reference"

    2. drag the Components reference over to the center so it appears under Fields

    3. in Components table add a text field named "Purchase Order Primary Key" (without quotes)

    4. make sure your Purchase Order field is of type "Yes / No" checkbox

    5. paste following code into "Trigger after update" of the Purchase Order field:

     

    let key := this;
    if 'Purchase Order' = true then
      let s := Specifications;
      let c := RRP;
      let t := (create 'Purchase Order');
      'Purchase Order Primary Key' := t.Id;
      t.(Components := key);
      t.('Item Description' := s);
      t.(RRP := c)
    else
      if 'Purchase Order' = false then
        let pk := 'Purchase Order Primary Key';
        delete (select 'Purchase Order' where Id = pk)
      end
    end

    • Anna_Wenlock
    • 4 yrs ago
    • Reported - view

    Thank you so much for this Dean, I've tried it out and it works perfectly!

    • Anna_Wenlock
    • 4 yrs ago
    • Reported - view

    Hi Dean,

    My apologies for bothering you again, but at least you'll have an idea of what I'm talking about below...

    I have done as you suggested and rolled this out to include some other tables, however, my team are feeding back that they would like the entries authorised via inspections to also show in other areas of the system...I swear they're never happy.

    Do you know how I can get the Purchase Orders added from Components to Purchase Order (as before) to also show here:

     

    Technically it is the same table, but this is the one originally used 'Reference from Workflow to Purchase Order (1:N)

    I'm assuming that I need to somehow recreate what you provided last time, or tell it to pull the data from the main table, but I don't know how.

    Basically, when I click Yes on the Purchase Order button, the line is created in Purchase Orders, but it does not show in the table located on the Parts Supply tab of the customer record in Workflow.

    I hope this makes sense?

    Many thanks, Anna

    • Choices_Software_Dean
    • 4 yrs ago
    • Reported - view

    Add a new 'Purchase Order' view and enter the following as its formula:

     

    select 'Purchase Order'

    • Anna_Wenlock
    • 4 yrs ago
    • Reported - view

    Thanks, Dean, that is far more straightforward than I was thinking. Obviously just overcomplicating it!

Login to reply

Content aside

  • 4 yrs agoLast active
  • 5Replies
  • 917Views