How to restrict a query with 2 data types that are not directly linked together

Hi,

Sorry my topic title sucks, I didn’t know how to describe this issue shortly ^^

I’m building a tiny NPS tool (for myself), which is already up and running.
In my database, I have categories, polls and answers, where answers are linked to a specific poll, which can be linked to a specific category.
Put another way, I have categories > polls > answers.

I’ve managed to display answers per poll quite easily, but now I’d like to display answers per category, although answers are not directly linked to a category but to a poll that belongs to a category.

So I have a repeating group displaying categories (so far so good), and I’d like to display the answers given to all polls in each of these categories, but have no idea how to do this.
I’m pretty sure I have to start with a “Do a search for Answers” but I don’t know how to restrict this request to answers belonging to the current cells’s category, as they are not directly linked to a category but to a poll in between. Is there a way to do it without making any change to my database?

Thanks in advance for your help (and hope it makes sense as English is not my primary language!)

Hi there, @dlepunski… if I understand your post correctly, an advanced filter that looks something like this should do the trick. Note that in this example, I have a repeating group for answers inside the repeating group for categories.

If you have never used an advanced filter, the Advanced option is all the way at the bottom of the dropdown when adding a constraint to the filter. Also, from a performance perspective, advanced filters can be slow because the filtering is done on the client side, so just something to keep in mind there.

Hope this helps.

Best…
Mike

1 Like

Yep, as @mikeloc suggests, with your current database structure (at least as you’ve described it) you’ll need to use an advanced filter for this…

Depending on how much data you have that may not be optimal from a performance perspective (but it might be fine… it depends on a number of factors, so testing it will be the best way to decide)…

Alternatively, you might want to look at restructuring your database to make it more efficient to do what you’re trying to do (i.e. add a category field directly to the Answer datatype - that way you can add a constraint on the search for Answers directly which will be simpler and likely faster than using an advanced filter).

2 Likes

Thanks Mike, that’s exactly the idea, and this advanced filter looks great!
However my repeating group is not a “Repeating group answer” but “Repeating group category”. Will this still work?

As I mentioned at the beginning of my response (you might not have seen it, though, because I added that sentence a few minutes after I posted the response), you can put a repeating group that shows the answers inside of the repeating group that shows the categories. The repeating group for categories would be a search for categories with no constraints, and the search/filter on the repeating group for answers would be what I am showing in the screenshot.

@adamhholmes Well I could add a category to an answer, but 1/ it doesn’t really makes sense from a pure logic perspective to link an answer to a poll to a category so I’d rather avoid it, and 2/ I like to understand what’s possible or not, and I’ve already learnt a few things here ^^

@mikeloc I’ve reproduced what you’ve said and created a repeating group of answers inside the repeating group for categories, but nothing shows up in this repeating group of answers, no matter what I put there (even a text with the default “edit here” text isn’t displayed). Am I missing something here?

Can you share some screenshots? It’s hard to say what the issue could be without being able to see what’s going on.




Sure, here they are: this is how I’ve set up the repeating group for categories, for answers (Réponses), the default text added to the answers repeating group and what I get on the front-end of the app.

If I am seeing things correctly, you have a text element in the repeating group for answers, but you haven’t specified what that text element is supposed to show. Try putting Current cell's and then whatever the field is in the Response data type in which you are storing the answers.

If you have already tried what I wrote above (it looks like maybe you did in one of the screenshots), check to see if you have any privacy rules set up that might be getting in the way.

@mikeloc I have already tried this indeed.
Now that you mention it, I do have an issue with privacy rules for answers. Actually I don’t really know how to set their privacy rules, as these answers must be visible for the poll’s creator although they’re not the ones who are answering the poll. The answers are added into the database when a user (anyone, really) clicks a link to a thank you page with the poll id and answer in the URL to fill in the cells correctly in the database.
Do you have a hint about what these privacy rules should be in this case?

1 Like

Well, I’ve removed all privacy rules for Answers and it works. Thanks a million!
If you have any piece of advice regarding these answers, that would be much appreciated though.

1 Like

It would be easier to help if I could take a look under the hood… so, if you are willing to share a view-only link to your editor, shoot me a private message with a link.

A question that comes to mind, though, is which users are not supposed to be able to see the answers?

Well, all other users that would create polls, I guess.
What I mean here is that if I don’t restrict the privacy of answers in the database itself, I will have to restrict the answers whenever they’re displayed with the same kind of filter that you’ve just helped me create, right (i.e an advanced filter where This Answers’ Poll’s Creator is Current user).
Am I getting this right?

You could restrict the answers on the front end, but the right way to do it is with a privacy rule. I believe the problem you are going to have is that you don’t have a direct correlation between a poll’s creator and the poll’s answers. Because of that, you are likely going to run into a well-known limitation of privacy rules, and that limitation is described in the editor as Rules that use “This Data Type’s X’s Y” can’t grant search access right now. If that limitation wasn’t there, I believe the rule you are trying to create is when This Answer's poll's Creator is Current User, right? If that’s the rule you need, then as I mentioned, you won’t be able to create it.

All of the above being said, you might want to consider adding additional fields to your data types so you can create the appropriate privacy rules and so you don’t have to use advanced filters. True, adding an additional field might not always make the most sense from a logic perspective, but if it enables you to do something that isn’t otherwise possible, then you should probably give it some thought.

@mikeloc Makes perfect sense, thanks a lot for your detailed answers on all this!

1 Like