JoeyBrannon

a tool can belong to more than one team so the relationship between tableTool and tableTeams has to be many-to-many. it might help to think of tools as training modules where certain modules might need pre-requisite modules to be completed first and some modules might not apply to a particular team at all. As a new team is onboarded there is a standard set of modules that will almost always need to be made available to the team. So we are looking for a way for the advisor to go in and quickly see which modules have been assigned to the team and which have not and then easily assign any of those that have not. The only way I know how to do a many-to-many relationship is through a third table that has fields for the tableTeams.teamID and the tableTool.toolID.

Here is the use case.... We have a list of teams that we work with (tableTeams) We have a list of tools that we have developed (tableTools) We would like to have an interface that lists all of the tools we have created with a blank checkbox next to each one Checking the box makes the tool available to the current team (the current team is passed to the datapage using a parameter). Unchecking the box removes the tool from the current teams "toolbox" --- I originally planned to tackle this using a many-to-many join table (tableToolAssignments) where fields for tableTeams.teamID and tableTools.toolID would relate teams to their available tools and I can still do that but I'm looking for a more intuitive and efficient interface. In other words I would just like to go down a list of checkboxes to check and uncheck tools as desired rather than create a new record by selecting a tool from a dropdown list to create each association.

That solution worked well. It is currently functional, but not super readable. Is there a way to add additional formatting to the table variable when it is inserted into the email as well as change the column headers to something other than the field name?

@CoopperBackpack what is the purpose of the Group By step when compiling the list of emails?

thanks @vidierre I think I got it. The logs were helpful.

I have two tables USERS TODOS The two are related using USERS.userID and TODOS.userID I would like to send a periodic email to each user listing all of their todo's in the body of the email. I am having a hard time wrapping my head around it because the only other time I have used a task to send an email I was just sending one email per record in the dataset. For this it seems I will have to use some sort of loop.

Our team does a lot of meetings with different clients. We have built an app that will track these meeting agendas and would like to automate the sending out of a meeting agenda to everyone who is on that particular team. I have the following tables MEETINGS USERS USERASSIGNMENTS The tables are related as follows MEETINGS.teamID < USERASSIGNMENTS.teamID USERS.userID < USERASSIGNMENTS.userID where < represents a one-to-many relationship I have setup a task that I would like to run every hour (currently running on demand during testing) that goes through the meeting list and finds any meeting records where the MEETINGS.sendRecap field is true. It would then send an email to all the users that are assigned to the team that had the meeting. A secondary function of the task is to go through the meeting list, set the MEETINGS.sendrecap checkbox to false and update a status field to indicate the email was sent. I have tested this with teams that have 1 user, 2 users and 3 users and every time it says it sends two emails but only one is ever received by any of the users and it is always the same user. Any help would be greatly appreciated.

@CoopperBackpack and @PotatoMato You guys are awesome! Thanks for helping a css neophyte!

I started testing today under the following scenario.... scheduled task that will run every day (during testing I am just running it on demand) The task essentially looks for a True value in the MEETINGS.sendRecap field It uses two inner joins The first joins the user ID's associated with the team having the meeting using USERASSIGNMENTS.teamID and MEETINGS.teamID The second joins the email addresses associated with the userID using USERASSINGMENTS.userID and USERS.userID I have tested this with teams that have 1 user, 2 users and 3 users and every time it says it sends two emails but only one is ever received by any of the users and it is always the same user.

@CoopperBackpack thanks so much. That did it. Is there also a way to eliminate the shading from the "cells" underneath the group headings so that it is easier to preserve the step-wise visual progression from group1 header, to group2 header to individual data rows?

The final approach was to house all the agenda templates in one table, including the defaults. There is a field in the table for teamID and the default agendas do not have a teamID (the teamID field is blank for default templates). Instead of a dropdown I created a gallery view datapage that filters for a matching teamID OR a blank teamID (default template). Clicking on a selection in the gallery view results in a detailed view with the templated fields to be used in a document hidden. These fields are set to pass parameters upon exit. On the destination options screen I am using "Destination after record update: Go to a new page" to send the user to a new url with a new form submission datapage set to receive the passed parameters and fill in the templated fields.

I have noticed this in all my tabular reports that use field grouping. How do I get rid of this partial border or make it go all the way across the row. I don't care either way but having it stop is very distracting when using conditional formatting to change the background color of rows based on field parameters.

@CoopperBackpack This is incredibly helpful! I can't thank you enough for going the extra mile to explain how the inner joins continue to expand the dataset. I get it now. I have decided to move from a triggered action to a task that runs periodically (daily or hourly) so that I can check the recapStatus field for a "Ready to send" value and then update it to "Sent" after the email action. Another question I have is what is the difference in the various dropdowns in the task between fields in the #record group that shows up first and those same fields in the "Meetings" group below (see screenshot)?

the url is http://myappdomain.com/meetingrecap?mid=..... where the mid parameter is the external parameter tied to the MEETINGS.meetingID field. In other words there is a web page on my website at myappdomain.com/meetingrecap with the datapage embedded in it. This datapage is a single record details report filtered for a specific MEETINGS.meetingID value. It gets that value using an external parameter from the URL. This way I can insert the url link in the body of the email by pulling the MEETINGS.meetingID field from the record.

I have the following tables MEETINGS USERS USERASSIGNMENTS The tables are related as follows MEETINGS.teamID < USERASSIGNMENTS.teamID USERS.userID < USERASSIGNMENTS.userID where < represents a one-to-many relationship I have a field MEETINGS.recapStatus that I would like to use to trigger an action to send an email to everyone assigned to the team. The idea in my head is that when a record has an update and the MEETINGS.recapstatus field = "Ready to send" the trigger will run and send an email to all the users with a URL related to that meeting recap (a url on my website with an embedded Caspio Datapage using an external parameter in the url to show the correct record in the Datapage. Things are complicated by the fact that the email addresses I need are in the USERS table, two tables removed from the MEETINGS table where this triggered action originates. I know this can be done, but it is my first task/triggered action and I could use some help figuring it out.