Forum Discussion

DebLanser's avatar
Qrew Member
3 years ago

Multi-select child records in many to many relationship

This is our scenario, a Customer has Displays, and a Display has Display Zones.
We create projects for a customer, and have a many to many relationship to link a Project to multiple Display Zones from displays owned by the customer.  We want to be able to have a list pop up of all the available display zones and select the ones we want on the project.

I have it mostly working.  I created a formula URL button to add a record to a User Work Table with the user, record ID of the project, and customer number.  It then redirects to a grid edit report built over a Zone work table.

A pipeline triggers when the User record is added.  It adds all the display zones for that customer, with record IDs, and the three fields from the first table to the Zone work table.  The user is already in the grid edit report and needs to keep refreshing until the data is there, which isn't ideal.  I couldn't find a way for the pipeline to redirect to the report after it finished loading.  Once the data is there, the user checks a select box for the zones they want added to the project.  When they click Save, a second pipeline is triggered when the Zone work table is updated, and creates the link records in the Projects to Display Zones table.

As a final step, I need to delete the records from the Zone work table but haven't figured out this part.  I have tried adding a third URL to the button but have not had any luck.  Does anyone have any ideas on how to do this?  This is the code in my button:
var text Token ="mytoken";

var text URLADD = URLRoot() & "db/UserWorkTable & "?a=API_AddRecord&apptoken=" & $Token
& "&_fid_6=" & UserToId(User())
& "&_fid_7=" & [Record ID#]
& "&_fid_8=" & [Order - Customer Number];

var text URLRPT = URLRoot() & "db/ZoneWorkTable?a=q&qid=5"; 

var text URLDLT = URLRoot() & "db/ZoneWorkTable" & "?a=API_PurgeRecords&apptoken=" & $Token
& "&query={11.TV.'_curuser_'}";

& "&rdr=" & URLEncode($URLRPT)
//& URLEncode("&rdr=" & URLEncode($URLDLT))   //This deletes right away, doesn't redirect to report
& "&NextURL+" & URLEncode($URLDLT)  // This doesn't delete the records

Deb Lanser

2 Replies

  • The URLDLT does work when I add it to another formula URL button.  It just doesn't seem to work when it is added as a third redirect.

    Deb Lanser
  • I believe it could be the way your redirects are setup 
    API Edit or Add button requires you to setup them in below format 

    a=doredirect&z=" & URLENCODE($URLRPT) or DLT as you fancy

    Prashant Maheshwari