Multiple keys in data store?

Questions & Answers
1

I would like to create a data store table which allows bidirectional mapping between pairs of objects in two external services. (In case you are curious, between ClickUp tasks and GitLab merge requests, although I don’t expect that is relevant to my question below.)

This mapping should allow looking up a record by one of the fields representing an object in one of the external services (e.g. ClickUp task id) and allow access to the field representing the other object in the pair (e.g. GitLab merge request id). This lookup should work both ways round.

When designing a scenario to create the mapping, I have tried using the data store’s “Add/replace a record” action to check whether a mapping exists for a given object on one side, and if so, update the mapping to the object on the other side of the pair, otherwise add a new record for the pair mapping.

However, this action is based on the usage of a primary key, but it seems that data sources allow only one key for lookups. So I can get the action working one way around (e.g. given a GitLab MR, add or update a mapping to a ClickUp task), but then I can’t do a lookup based on the second id on the other side (e.g. add or update a mapping from a GitLab MR to a Clickup task).

Is this a fundamental limitation of the built-in data stores? It seems strange that you can’t do a lookup based on other fields in the data store, although maybe this was done as a way of avoiding having to do lots of expensive indexing in the backend databases, which at make.com’s scale I imagine could be a big issue.

If I’m right, then what are the possible workarounds? So far I’ve thought of:

  • Maintain two separate data structures, one for each side of the mapping, but this seems messy, extra work, and problematic.
  • Store the mapping elsewhere, e.g. a Google Sheet. But so far this seems extremely awkward, since there is no equivalent “add or update” action so it looks like I’ll need a switch depending on whether a row already exists to handle add and update as two separate actions.

Is there an easier way?

Thanks!

2

@aspiers

I would be inclined to save a single array that describes the mapping in the datastore (1 record, 1 key).

After the map is read, the mapping reference would look something like this:

image
image735×180 11.3 KB

And, of course, to go the other way:

image
image708×143 10.6 KB

Jim - The Monday Man (YouTube Channel)
Watch Our Latest Video: Creating monday subitems using Make/Integromat The Monday Man Way
Contact me directly here: Contact – The Monday Man

1 Like
3

Thanks a lot! I’ve read up on arrays of collections and map() and this makes sense, so it’s great to know this is an option. However this seems to have downsides:

  • Based on the list of array functions, it seems there is no equivalent of the “Add/replace a record” action, or even of a “Replace a record” action. So do I understand correctly that to update a mapping, I would have to first use map() to check for existence, and then if it does exist, use remove() before calling add()?
  • An array of collections does not guarantee uniqueness of either field.
  • Storing it in a single record with a single key prevents being able to easily browse the mapping via the data store’s Browse feature.
  • Performance - presumably map() has to iterate over every item in the array, which is linear O(n) complexity rather than O(1) constant-time complexity (although for small array sizes this should be a non-issue).
4

Well, I finally got it working with Google Sheets after a lot of experimentation. I would share if I knew how - can’t make a template since I’m not on the Teams plan, and exporting a blueprint JSON file contains stuff specific to my Google account (including my email) which I’d need to censor I guess.

5

@aspiers

  1. Yes, arrays must be effectively “rebuilt” to change them
  2. Correct. YOU would need to maintain the “uniqueness”.
  3. Correct.
  4. If you are suggesting that the array mapping approach would be slower than multi-keyed datastore, I doubt it. But the only way to know for sure would be to test it.

With regard to sharing what you did, usually, a screenshot of the scenario is a good start.

Jim - The Monday Man (YouTube Channel)
Watch Our Latest Video: Creating monday subitems using Make/Integromat The Monday Man Way
Contact me directly here: Contact – The Monday Man

6

Sure:

image
image1746×287 54.6 KB

The key “tricks” were:

  • The spreadsheet containing the mapping has a first column whose cells are set to the ROW() formula if that row is non-empty. This means that if the “Check existing mapping” module finds an existing mapping match, the row number in the sheet for that match can be returned as part of the module’s output.

  • “Check existing mapping” does the query:

      select A, B, C, D where D = "1.object_attributes.url"

    to see if the sheet already has an entry for the given GitLab MR.

  • “Count existing entries” does the query:

      select count(B) where B != ""

    to count how many rows there already are (maybe the where clause isn’t even needed here).

  • “Calc row number to update” does:

       if(ifempty(3.`__IMTLENGTH__`; 0) > 0; 3.`0`; parseNumber(4.`0` + 2))`

    so that if a mapping was found, it returns the existing row number (this is the 3.`0` bit), and if one isn’t found, then it returns the number of existing data rows plus 2 (add 1 to take into account the first header row, and 1 more to ensure we’ll append a new row after the existing rows). The ifempty() and parseNumber might both be overkill, but I was seeing some weird results without them.

This way, I can have only a single module which handles both add and replace cases, which means I can avoid duplicating the configuration for populating each field.

I did previously try using a router as an if/else to handle add and replace separately, and that can work fine too, but I think I prefer this approach.