Field relationships don't support referencing multiple bundles

[TravisCarden]

You can't currently pick more than one value for the "Ref. bundle" field (target bundle for reference fields) on the "Fields" tab because Google Sheets validation doesn't support multiple dropdown selections. In the meantime, additional values can be hand typed, separated with commas. It will capture the necessary details for implementers, but the cell will get flagged as having an error, and it will break the entity relationship diagram (ERD). Obviously, though, a cleaner solution is desired longterm.

[greggmarshall]

Would the technique in this solve the multiple select problem (haven't had time to evaluate it)
https://gist.github.com/arthurattwell/aa5afc178624bdd6f46c2d0d28d06136

[TravisCarden]

That looks like an interesting possibility, @greggmarshall. It preserves the out-of-the-box interface for the simple use case but supports the complex use case with reasonable added complexity in the UI. I like it. This will be worth looking into, time allowing.

[NickDJM]

It's worth noting for this, that Google sheets supports "chips" as a dropdown option now- which also allow for multiple selections.

We solve this by just changing the referenced entities filter display to "chips" and then check off "allow multiple selections"

[TravisCarden]

Thanks, @NickDJM! I saw the new "chips" feature not long ago and immediately thought of this issue. From your experience, what effect does it have on the dynamic columns and values throughout the tool? Do you use the PlantUML diagrams or the Behat tests? I'd be shocked if they still worked, but if we can get them to, I'll absolutely implement this feature!

[NickDJM]

I'll take a look at how everything works and see if I can get the plantuml and behat working- it definitely does not right now.

As an example, something that references multiple entities ends up having things like this show up in plantuml:

[NickDJM]

As an update: I looked at how to PlantUML structure is generated, and I'm not entirely sure if google sheet's queries can handle this- granted I don't often delve into complex queries.

The issue I've run into current is as follows:

Lets say we have a content type called "page", and we have a field called "related content" which allows us to references other pages and another content type "event".

In the spec sheet we have this set up with chips (and this works fine).

The output of the chips is: "page, event"

The current query that makes the entity relation diagram outputs: page_content_type --> page_content_type_event_content_type

So it does recognize that there are multiple values reference, but it merges them into one.

Altering the query to:

={
  "Relationships";
  IFERROR(SORT(FILTER(
    LOWER(REGEXREPLACE(REGEXREPLACE(Fields!B:B, "([\(\)]+)", ""), "([^A-Za-z0-9]+)", "_")) & " --> " & LOWER(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(Fields!J:J, "([\(\)]+)", ""), ",\s", ","), "([^A-Za-z0-9_,]+)", "_")),
    ROW(Fields!A:A) > 1,
    Fields!B:B <> "",
    Fields!J:J <> ""
  )), "' No relationships.")
}

Changes the output to: page_content_type --> page_content_type,event_content_type

Which is a step in the right direction, however PlantUML will require each entity reference to be on it's own line, so I'll need to alter the query to get it to output something like:

page_content_type --> page_content_type
page_content_type --> event_content_type

This is where the tricky part is. I'm confident I can reverse some of the replace logic so that we add the page_content_type --> in front of each item. What I'm not sure on is if we can successfully output multiple lines like that.

[TravisCarden]

We're going to get to the point (if we didn't get there long ago) that we're going to have to start writing an add-on or Apps Scripts or something. I've resisted making the leap, just because I have no experience with it and little time to give to it, but it has long been a concern of mine that there's so much logic embedded in cell queries that are user-accessible and can't be version-controlled.

[NickDJM]

Yeah, that's a fair concern.

The change needed to support outputting the format needed for multiple entity references is pretty big.

The current query is hard enough to read inside of a cell as it is- I had to move it out into a file just so I could add proper indentation and line breaks to read it. Adding another level of loops and outputs to it would just compound the problem.

I suspect it'll become significantly easier to read, understand, and maintain if moved it to a script.

---

I've written some rudimentary app scripts for gmail and such before. When I have some time, I can take a look at what it would take to implement.