0

Need help automatically linking tables.

I have the following code. Explanation below. 

let current := Id;
for j in (select Workorders).'Related Equipment'.Equipment do
let Newinspectionsheet := (create 'Inspection Sheet');
Newinspectionsheet.(Date := current.Date);
Newinspectionsheet.(Workorders := current);
Newinspectionsheet.(Equipment := Equipment.Id)
end

there are 4 tables involved in this  

Workorders has the button with above code

Equipment 

Related equipment which is a junction table between Workorders and Equipment  

inspection Sheets which I'm making new records on  

pseudo code as follows  

for everytime current workorder links to equipment in related equipment table(unique piece of equipment)

create new record in inspection sheets

Set date of inspection sheet to same as current workorder record

Link new inspection sheet record to current workorder record 

link new inspection sheet record to equipment record

5 replies

null
    • James_Sullivan
    • 4 yrs ago
    • Reported - view

    Sorry for formatting. Typed on phone. 

    • James_Sullivan
    • 4 yrs ago
    • Reported - view

    The problem I'm having is that it will create more records than there are links.  Also it links the workorder fine but does not link the equipment. 

    • Mconneen
    • 4 yrs ago
    • Reported - view

    Only glancing at your for loop... j is Equipment.. 

    so should 

    Newinspectionsheet.(Equipment := Equipment.Id)

     

    really be

    Newinspectionsheet.(Equipment := j)

    • James_Sullivan
    • 4 yrs ago
    • Reported - view

    Yes thank you Mconneen

    This has fixed the linking of the Equipment, however I still have a problem with my loop selection.  The way I had it selects all records.  Instead I want it to only select the linked tables that match the current ID

    I'm currently trying

    for j in select Workorders.'Related Equipment'.Equipment where Workorders.'Related Equipment'.Workorders = current 

    But the editor is returning unexpected keyword: where at line 2, column 73

    Anyone have any ideas what's happening?

    • James_Sullivan
    • 4 yrs ago
    • Reported - view

    Thank again Mconneen.

    I've ended up fixing this by using an if statement in the for loop.  I wish I could select only the records I want since I'm worried this button will really slow down in the future but its a fix for now.

    If anyone is scowering the forums in the future for a solution Like I've been the last few hours.  Here is what worked for me.

    let current := Id;
         for j in (select Workorders).'Related Equipment' do
    if j.Workorders.Id = current then
              let Newinspectionsheet := (create 'Inspection Sheet');
              Newinspectionsheet.(Date := current.Date);
              Newinspectionsheet.(Workorders := current);
              Newinspectionsheet.(Equipment := j.Equipment)
         end
    end

Login to reply

Content aside

  • 4 yrs agoLast active
  • 5Replies
  • 621Views