Table 1 is the user table. Each user has a distinct email address. In addition each user is a member of one group (e.g. Group 1, Group 2, Group 3).
Table 2 is a list of items. Each item in that table has a unique number. Some of those items are assigned to one ore more users based on their group. In addition, for each item in the table that has been assigned to an upcoming meeting there is a unique identifier for that meeting.
Table 3 is a list of upcoming meetings in which the items in Table 2 will be discussed. This table has one column in which the unique identifier for the meeting is displayed and then a relation column in which all of the items from table 2 that match that identifier are matched.
I’m trying to fashion a scheduled workflow that each day will iterate through the user table and send a customized email to each person in the user table displaying the upcoming meetings at which their item(s) will be discussed. I’ve already devised a working solution to how to display the content in the email (using a join that looks through a template of html to provide each item in a bulleted list) But I’m struggling with how to devise a query that provides the right content, since this involves content from three different tables.
For example, I can write a query in the user table that shows all of the items that match the user’s group, using the “this row” function. But then, I need to ask Table 3 “ok, of the items in my user table query, which of those items are being heard at each meeting”. I think I’m missing some logic to queries because it seems like you can’t say “Take this query of table 1, then execute a second query to find a subset of query 1’s items based on content from table 2, and finally take this table 2 query, compare it with data in table 3 based on further criteria and give me the result…”
I’ve tried endless approaches on this. Would love any ideas. I think I’m missing something very basic.
One issue that is coming up as I try to implement your example is that in your example in the items table, each item is assigned to one group. In mine, it can be assigned to multiple (I.e. 1,2). When I try to create the lookup you used in the query toward the end (that you needed to adjust mid video) the 1,2 becomes an array in which the content is [1,2] as opposed to [1] [2]. I tried splitting the org column in items to make an array of each item but I can’t use a lookup to retrieve the data of that computed column. I should know this but not sure how to fix this so that I have a column with two distinct values that I can use in the filter to say lookup value–> “includes” -->this row–>org.
So after testing this more, this approach got me closer but there is still an important element missing, the part I was trying to describe in my “For example,” paragraph. I want to produce an output that not only lists the meetings at which a particular group’s items will be discussed. I also want that list of items to be further filtered so that under the meeting heading, it lists ONLY the items that are assigned to that group. I cannot figure out how to further filter it to accomplish that. So for example in @Robert_Petitto’ first video, at about the 1:30 mark, we see the join displays items 2, 5, and 8 under M2. However, for user Bob, only item 2 is assigned to his group (group 1). How can I further filter this so that the Join All that results and is sent to bob would only list his item (item 2) under the M2 heading, not the other ones not assigned to him.
Just to close the loop on this if anyone is attempting something similar, my solution (for now, until I come up with something better) is to rely on a helper table in order to further filter the list of items that have a meeting to only match those items that both have a meeting AND are on the user’s list of items.
So now the workflow loops through the user list, grabs that user’s list of items that are are assigned to that user (created using the method suggest by @Robert_Petitto) creates a new row in the helper table and writes that list of items to the helper table. In the meetings table I use a single value column to grab that list and then a query to find, for each meeting/row, the items that have a meeting that are included in that users list. It then use the html templates and join functions to creat a formatted list of that person’s items, grouped by meeting, puts that into the body of the email and sends the email. It then deletes the row in the helper table and then continues to loop through the user table, repeating that process.
Obviously this is a little costly in terms of updates since the new row and delete row in the user table each use an update but so far that’s the only solution I’ve come up with. Thanks again Robert for your kind help.