Intermediary Object Question

[michaelstancil]

Hey all,

I'm trying to figure out a way to keep a dataset that I need to access, but not all the values may be linked to anyone in my database...yet. The data set I have is keyed/linked off a zip code. There are then five interesting fields about that zip code I'd like to store. However, I'm trying to keep as many zip codes as possible to reduce the work in the future, which means some zip codes won't be tied to anyone. When I've tried to upload to my current custom object, only the zips that match a zip are updated.

From my research, I think an intermediary/bridge/edge relationship will solve this. It seems as though I should create a new edge object that has all of the zips + five interesting fields, and then a bridge object that links to the lead zip code, and then the edge object zip code. However, the part that is confusing me is now I don't know how to access those five interesting fields?

Is the answer simply that I need to create two edge custom objects that are zip + two fields and zip + three fields (given the three link max) and then link/dedude on all of those?

[Jo_Pitts1]

@michaelstancil ,

what's the use case for this.

Also, what CRM system are you syncing with?

Cheers

[Darshil_Shah1]

I am wondering if you need a link field in true sense here? Would it not be possible to create a custom object without a link field, store all the Zip code and interesting data about it in it - you could query the CO data based off a matching zip code search in the velocity (assuming you're not gonna trigger any flows as records are added to this CO since its a standalone zip + info database)? Zips could easily be duplicated/repeated across multiple leads in your DB, in that case the corresponding CO record would not be linked to all the people with the same zip code in case you're using the zip as the link field, if you change the link field, to say email/lead-id you would have to create separate zip CO records for each person with same zip.

[Katja_Keesom]

It would not really be possible to use the zipcode as a link field anyway, as that would mean the CO record is only going to matched with one record in your lead database with that zipcode (usually the record that was last updated).

Depending on the set up of the integration, I would actually opt to add these interesting fields directly to the lead / company object.

[Darshil_Shah1]

Agreed, that's what I mentioned about using zip as the link field!

However, creating the custom fields (which is definitely one of the possible solution to manage and setup this)  would require the same interesting data to be updated on all the leads having same zip codes and this would have to repeated for all the zip values - and this exercise would in-turn have to be repeated each time interesting data about any of the zip changes. Having a standalone CO would comparatively be easier to manage and update data corresponding to the zip. But if the use-case requires this data being updated from the Marketo flows too along with integration/API calls it would make more sense to create the fields and do the heavy lifting of updating/managing person records with the data. Again, all down to the actual use-case! 🙂

[michaelstancil]

@Jo_Pitts1 CRM is Salesforce, but given limitations and speed restrictions on that team it might as well be CRM agnostic. The use case is that I want to send these interesting fields within the email content to users who have this zip code on their record. 

@Katja_Keesom My concern with setting it up on a per lead record is that many records will have the same zip code, so I want to cut down on the duplication.

@Darshil_Shah1 So I create a master CO that isn't linked to anything right now that is currently setup this way. Nothing is triggered from the CO, I just want to look for the zip code and then pull the five fields. I think what you're saying makes sense, I just don't know how to access it given that there's no link on it, it doesn't show up in the side field within my email scripts.

[Darshil_Shah1]

Is your CO approved? AFAIK, You cannot not see the CO in the email script tree until it's approved.

[michaelstancil]

It is approved, but I'm assuming that it's not showing because there are no linked fields?

[SanfordWhiteman]

---

It is approved, but I'm assuming that it's not showing because there are no linked fields?

---

Correct. You can’t access unlinked objects from Velocity.

Moreover, you couldn’t access the details of the edge objects from Velocity, even if you did have the junction objects in place.

Now here’s a question: how many bytes of data do you anticipate storing across all the ZIP codes? And can you give an example of the 5 data points so we can see how the data might be most efficiently stored?

[michaelstancil]

For the sake of simplicity, let's take it to four items:

1. Zip (five digits)

2. Cost One: (six digits)

3. Cost Two: (six digits)

4. Count: (two digits)

The fifth item was a percent change between #2 and #3, but I can do that calculation elsewhere.

As for total size, a CSV with ~6,000 records (of the four above) is 200KB, so each row of four items is ~33KB.