MIS homework

Max's Distinctive, Impressive BizTech Student Blog

T h u r s d a y , N o v e m b e r 3 , 2 0 1 6

Data Fright II: Like Deja Vu All Over Again

So you know in scary movies, how at the end, the monster/bad guy/whatever finally gets killed in some heinous fashion and everything’s all good and everyone’s all relieved but then, magically, he pops up & starts terrorizing again one last time? And you can kind of tell, just before it happens b/c things seem a little too ok? (And the credits didn’t start rolling yet?)

So yeah, welcome to my reality. After my last post, I knew my app was way better, more logical, but I had this sneaking suspicion that something still wasn’t quite right...something heinous was still lurking in the shadows. Something...clone‐y. Hate that thing. Ok. So.

It was a dark & stormy night...(not really, but...)...I was all alone in the house (ok, my dorm room, actually) and something made me open up SF. Slowly I typed. Tap...tap...tap. Follow along if you dare. Those with heart conditions may wish to refrain.

First, to truly see how the data monster rose again, we need to fill in the rest of your Meeting data. Don’t worry. I did all the typing so you don’t have to. (After making you type stuff in & then delete it last time, hey, I owed you.)

So I made an import file of data that was sitting in Pitch before we knew it didn’t belong there —Contact Name, Email Address, Comments, etc. so you can just import it straight into that new Meeting object you added last time. (Oh, and I also added data values for your Enthusiasm field, to be super‐complete.)

Now, this import’s a little trickier than the one you did before back in the Slicer/Dicer post b/c you have to connect the Meeting records to the Pitch records they go with, so be safe—don’t text & type! Just follow closely & no one gets hurt:

Copyright © 2017 The Max Labs Project

1. First, click the link below to download the Meeting data file. (If you’re using Windows and certain browsers, you might need to option‐click the link and select Download. If it opens in Excel—just save it out as a file with the .csv extension. You just want to place it on your desktop or somewhere you can find it a few steps from now to bring the data into SF.)

http://tiny.cc/MaxsMeetingData

2. Then, in SF, go to Administer > Data Management > Data Import Wizard

3. Click on the green Launch Wizard button

4. Under “What kind of data are you importing?” click the Custom Objects tab. That should show Meetings and Pitches below.

5. Click on Meetings this time. That will open a middle box labelled What do you want to do?

6. Click on Add new records

7. ***NOW*** the slight twist—near the bottom of the center panel, where it says Which Pitch field in your file do you want to match against to set the Pitch lookup field?, pop open the menu labelled ‐‐None‐‐ and select Pitch Name

8. Then in the right hand column of the page, you’ll see where you can drag that .csv file onto the panel labeled Drag CSV file here to upload so drag and drop it there now

9. Once you have the file selected properly, the green Next button that was dimmed before should be bright, inviting you to click it, so go ahead and click Next

10. The next page that appears, Edit Field Mapping: Meetings, should show a perfect match between the SF Meeting object’s field names and the column headers in the .csv file and it even shows you samples of the data that will be going in

11. Click the green Next button

12. The next page that appears should say you have 7 mapped fields (and 0 unmapped ones) and then you can just click the green Start Import button

13. A popup window should appear with a congratulations and you can just click OK

14. You’ll get an email saying your import is complete

15. Then you should see a page of stats you can ignore about the import

Now, to make sure that all worked, you need to make a Meetings tab:

16. Go to Setup > Create > Tabs  

17. Under Custom Object Tabs click New

18. Pull down the Object menu & select Meeting

19. Click the magnifying glass next to Tab Style & click on the cute little red Presenter dude

20. Click Next, Next & Save

Now create a view that’ll show everything in the Meeting records:

21. Click your new Meetings tab, up there next to the Pitches one & click Create New View

22. For View Name enter “All Meeting Data”

23. Tab to autofill View Unique Name

24. Scroll down to the Step 3. Select Fields to Display section

25. In the Available Fields list, click Meeting Date to highlight it and then click the right arrow button under Add to move it into the Selected Fields list

26. Repeat the last step for Contact Name, Email, Mobile, Enthusiasm & Comments

27. Scroll down and click Save

Now you should see all the Meeting data with the three records you created last time for the meetings with Julia Chavez at the top. Below that—ten new records of meeting data taken from fields that weren’t supposed to be in Pitch records, eg. the contact info, comments, etc. Ok. They’re all there but are they really each connected to the right Pitch records? Let’s see:

28. Click on Edit for the 9/15/16 meeting with Maria Valducci

29. The meeting details page shows this meeting is connected to Pitch P‐007—click the little magnifying glass just to the right of the Pitch slot so you can see if that’s the right one

30. In the popup window that appears, you can see that Pitch P‐007 was made to Valducci Amalgam—yep, that’s her angel firm, alright. Amazing, eh?

31. In the Search... textbox at the upper left of the popup window, replace the highlighted “P‐ 007”  with “P” and click Go! (matches all Pitch IDs that start with “P”)

32. Now you’ll see a list of all the Pitch records and you could click one of those Pitch IDs to reassign this meeting to a new Pitch—go ahead! Pick a wrong one if you want—it’ll be ok!

33. Now you’re back on the edit page so just click Cancel so you don’t save your change

Just to be sure, check that Pitch records are connected to the right Meeting records too:

34. Click on any one of the Meeting ID’s in the list

35. On the Meeting Detail page, click on the Pitch ID for that meeting to open that Pitch Detail page and find the related list of Meetings section you added to that layout last time

Slick, eh? Seems like life is all good. We killed the dupe monster at the end of the last episode, right? But wait...the credits aren’t rolling yet <sigh>:

36. Click on your Meetings tab, make sure the View menu says “All Meeting Data” & click Go!

37. Click on the Contact Name header to sort the records alphabetically by Contact Name

Now take a closer look at the Julia Chavez records. Notice anything...uh...dupe‐y looking?

Remember, at the end of last time, you were pretending to be Riley. You had three meetings with Julia Chavez so you created the first meeting record and then cloned it twice to save time, right? (My bad—I told you to.) But you were duplicating her phone number and email address each time. See?

What if I tell you she just changed her phone number to (408) 812‐1223 or has a new email address? You would have to make sure you change it in all three of her meeting records or...yup, lost integrity again. That’s what happened with my app for Riley. Major :(

rhee...rhee...rhee

Breaking Up, New Relationships & Becoming Normal(‐ized): A Database Therapy Session

Last time, getting the meeting fields out of the Pitch object got rid of duplicated street address data for the businesses—one address for one pitched firm record, connected to all its meeting records—the famed “one to many relationship,” right?

So why was there still duping happening? Finally lightning hit me (not literally, thank god)—One contact has one phone number, one email, etc. but can attend many meetings. The “many meetings” part is what’s causing the duping, just like with Pitch before.

Solution? Another breakout—Contact needs to be its own entity (object) too! One name, email and phone number for each contact, stored once and connected to that contact’s meeting records by a relationship so you can still find the contact info for who was at a meeting when you need to.

In SF, you’ll just replace all the contact info fields in the Meeting object with a single lookup field for Contact, where you can “look up” the contact info for a Meeting record when you need to.

Oh, and there’s one nice surprise to save you time. Turns out the Contact object is already there. It’s a built‐in (“standard”) object in SF, so you don’t have to create one. And it’s already got most every field you could ever want. Since you can keep the contact info in Contact from now on, just delete those fields from Meeting:

38. Go to Setup > Create > Objects > Meeting

39. Scroll down to Custom Fields & Relationships and click Del next to Contact Name

40. Click the checkbox for Yes, I want to delete the custom field. & then click Delete

41. Do the same thing to get Mobile and Email out of the Meeting object

Yay, duplicate data destroyed by arranging the data model to reflect the underlying relationships among the fields—“normalization,” Prof says.  But you will still need to be able to find the contact info for the people at meetings when you need it, so you have to connect up the Meeting records to their corresponding Contact records. Easy—just add a Lookup Relationship field to Meeting:

42. Still on the Meeting object page, under Custom Fields & Relationships, click New

43. Select Lookup Relationship & click Next

44. On the Related To drop‐down menu, select Contact (NOT ContRact!) and click Next

45. Field Label shows “Contact” but change it to “Contact Name”

46. Tab to autofill Field Name

47. Check the checkbox for Always require a value in this field in order to save a record b/c, honestly, a meeting really needs to have someone for you to meet with otherwise it would be woefully pointless, right? (That little bar on the E/R diagram, the 2nd nearest one to Contact, says there has to be a Contact record associated with every Meeting record.)

48. Click Next, Next, Next & Save

Now that Meeting records can connect to Contact records, you can find one of Julia’s meetings and, using that, you can create her Contact record & connect it to that meeting at the same time:

49. Click on your Meetings tab

50. Select the All Meeting Data view and click Go!

51. Under Action, click Edit for the 10/7/2016 record (that was the 1st meeting with Julia) 

52. On the edit page, find the new Contact Name field you just added—the one that connects to the Contact object record—and click the magnifying glass to popup a Lookup window

53. There are a bunch of fake contacts in there—SF comes with some for practice—but not Julia Chavez yet so you need to add her by clicking New

54. Now fill in the First Name and Last Name slots with...“Julia” and “Chavez” of course

55. Enter “[email protected]” for Email

56. Enter “(510) 403‐8995” for Phone

57. Skip the other fields & just click Save at the bottom to create Julia’s new Contact record

58. Back on the 10/7/2016 Meeting page, Julia’s name is there now so click Save for that record

Now that Julia’s got a record in the Contact object you can easily connect her other meeting records to it. First, connect up the 10/21/16 meeting:

59. Click Edit for the 10/21/16 Julia Chavez Meeting record

60. Click the magnifying glass next to Contact Name

61. In the Lookup window that pops up, “Julia Chavez” should appear (if not, you could easily look her up by typing “Chavez” into the Search... slot) & just click on her to fill in the slot

62. Now you’re back on the meeting record edit page and just click Save

Now you’re back on the list of Meeting records and you need to connect the last Julia Chavez meeting record, the 10/31/16 meeting record, to her Contact record:

63. Click Edit for the 10/31/16 Julia Chavez Meeting record

64. Click the magnifying glass next to Contact Name

65. In the Lookup window that pops up click on “Julia Chavez”

66. Now you’re back on the edit page and just click Save

Cool. Now to be complete, you’d still need to create Contact records for the rest of the contacts & connect the meeting records to them, but let’s not take the time. You get the point, right?

See, by eliminating the fields that had been duped for contacts with multiple meetings like Julia, we got rid of the duped contact data (eg multiple copies of her email address & phone). Now, those things appear just once on her one Contact record. Yay. The good ole one‐to‐many relationship saves the day, right? Wrong (sorry). I thought so too but...credits still aren’t rolling quite yet, sorry.

Let’s say you’re Riley again, like last time, and after your 3rd meeting with Julia at Spray & Pray, she emailed you—says she invited two of her colleagues at the firm to join you & her at the next meeting, on 11/15. Hmm...now you have three contacts for this one meeting. Which one’s Contact Name would go in the lookup field for the 11/15 Meeting record?

One Many‐to‐Many is One Too Many

So now the sad truth comes out! One contact can be at many meetings (got that covered) AND one meeting can have many contacts attending (not covered—ouch)! Not a “one‐to‐many” after all—it’s the dreaded <dum‐da‐da‐dum> “many‐to‐many,” scourge of the database universe. Oh, sure, you can draw it on paper in your E/R diagram. But can you build a many‐to‐many in SF or in any other database system? Uh‐uh.

And even if you could, where would you put the Enthusiasm field? It’s supposed to tell how enthusiastic one specific contact was at one particular meeting. It can’t stay with Meeting (every contact at that meeting gets their own value). And it can’t go with Contact (each one gets multiple Enthusiasm ratings, one for every meeting they attended, like Julia Chavez did). Soooooo? Two words: “junction entity.”

Prof told us many‐to‐many relationships between two entities can be replaced by two (2) one‐to‐ many relationships that link both entities to a “junction” entity, inserted in‐between. We’ll call our junction entity Meeting Attendance. Then Contact and Meeting would each have a one‐to‐many relationship to it, instead of a many‐to‐many relationship directly to each other. A Meeting Attendance record could show that this one Contact (say “Julia Chavez”) attended this one Meeting (on 10/21) and that day she was super enthusiastic (e.g. a “5”). Here’s the new pic:

In case you don’t know the markings, they go like this:

a crow’s feet symbol means “there might be many of these for one of those (at the other end of the relationship),” 

a circle means “there might be none of these for one of those,” 

a bar closest to the entity means “there can be no more than one of these for one of those,” and

a bar farther from the entity means “there has to be one of these for every one of those.”

So yeah, a Pitch might not have any meetings set up yet or it might have several. But, going the other direction, one particular Meeting had better be for exactly one Pitch. No more, no less. 

A Meeting might have a bunch of Meeting Attendances, one for each of the contacts that you met with at that meeting. But there better be at least one.

A Meeting Attendance record definitely needs a Meeting to connect to AND a Contact to connect to, too. Makes no sense to record a Enthusiasm rating without both of those, right?

Now, to make the E/R diagram happen in SF, you’re going to need to create a junction—a custom object called Meeting Attendance with just an Enthusiasm field and two lookup fields—one for Contact and one for Meeting, to make the connections (relationships). So one Enthusiasm rating goes with one combination of one Contact and one Meeting. Piece‐a‐cake.

K, so first create your junction object, Meeting Attendance:

67. Go to Setup > Create > Objects > New Custom Object

68. For Label enter “Meeting Attendance”

69. For Plural Label enter “Meeting Attendances”

70. Object Name should be autofilled already as “Meeting_Attendance”

71. Edit Record Name to be “Meeting Attendance ID”

72. For Data Type: Auto Number

73. Display Format: {0000}

74. Starting Number: 1

75. Just in case, check the Optional Features boxes to Allow Reports and Allow Activities

76. Click Save

Now, add the relationship‐making fields to Meeting Attendance–one connecting it to Meeting, and another connecting it to Contact, like in the E/R diagram above. Like before, you make a one‐to‐ many relationship by adding a custom field at the “many” end so each record there will contain the ID number of the linked record at the “one” end. So first, add a field to Meeting Attendance for connecting it to Meeting:

77. Under Custom Fields and Relationships, click New, then select the data type Lookup Relationship, click Next, and then, on the Related To > menu, select Meeting to add a field (Field Label = “Meeting ID”) that will be used to connect each Meeting Attendance record to the right Meeting record (check the Required checkbox, the Nexts & then Save & New)

Now repeat the last step but this time add the Meeting Attendance field to connect it to Contact (careful! ‐ do NOT choose ContRact by mistake!):

78. Use the data type Lookup Relationship again but then, on the Related To > menu, select Contact to add a field (Field Label = “Contact Name”) that will connect these Meeting Attendance records to the right Contact records (check the Required checkbox again, the Nexts and then Save & New again)

79. Note: For both of those fields, SF automatically checked Don’t allow deletion of the lookup record that’s part of a lookup relationship. That’s good. It prevents someone deleting a Meeting record that a Meeting Attendance record is connected to. Can’t have a meeting attendance with no meeting—bad “referential integrity,” Prof calls it. To delete a Meeting record, just delete all it’s Meeting Attendance records first. (Same w/Contact records.)

Finally, Enthusiasm. We already have an Enthusiasm field in the Meeting object, but now we know it belongs here in Meeting Attendances instead b/c it’s unique to each combination of a Meeting and a Contact at that meeting. We’ll delete it from Meeting in a sec, but first add it here:

80. For data type pick Number, for Field Label enter “Enthusiasm” and set Length = 1, Decimal Places = 0 & then click the Nexts & then Save

Now, back to the Meeting object. It’s still connected directly to Contact and we now know that’s wrong. To disconnect it, just delete the Lookup Relationship field you added there before. And then delete the Enthusiasm field (b/c it’s in Meeting Attendance now), so:

81. Go to Setup > Create > Objects > Meeting

82. Scroll down to Custom Fields & Relationships and click Del under Action next to the Contact Name field and finish off the deletion

83. Now go back to Custom Fields & Relationships and click click Del under Action next to the Enthusiasm field and finish off that deletion

Now the database structure in SF (the “schema”) matches the E/R diagram & Riley can add all four contacts for the next meeting with Spray, each with their own enthusiasm ratings, & no duping! 

Cool. But...uhm...the data’s all broken up. Is that ok? What if you wanted to get email addresses for all contacts for pitched firms in Mountain View, for example, to invite them to a demo reception you’re holding downtown next week? Could you?

After a Breakup, Can Fields Still Be Friends? (& Hang Out Together?)

Totally. The formula data type lets you add “virtual” fields that trace across relationships to bring in values from other objects! Let’s create some virtual fields in the Meeting Attendance object so, when it’s displaying a record, it pulls in the values we need from the Contact object (Email) and from the Meeting object (Pitch ID) which then pulls in the corresponding values of Address from the Pitch object. Then we can use a view to extract just the ones with Mountain View addresses:

84. Go to Setup > Create > Objects > Meeting Attendance

85. Under Custom Fields & Relationships click New

86. For Data Type select Formula and click Next

87. For Field Label enter “Email” and then tab to autofill Field Name

88. For Formula Return Type select Text and click Next

89. Make sure the Advanced Formula tab is selected

90. Click the Insert Field button to open a popup window with Meeting Attendance > highlighted in the listbox on the left (the > means “this one connects to more fields”)

91. In the listbox on the right, you see the list of Meeting Attendance fields, including lookup fields you can follow to select fields from the objects at the other end of those relationships

92. Click Contact Name > (here the “>” means the relationship to Contact) to open a third listbox of fields at the other end of that relationship which can now be brought in virtually, so corresponding values from Contact will appear when a Meeting Attendance record is being viewed

93. Scroll down in that third listbox to find Email and click to highlight it

94. A fourth box will appear saying you have selected Contact_Name__r.Email which, to SF, means “follow along the Contact Name relationship to Contact and get the Email value”

95. Click the Insert Button

96. Click Next, Next, Save & New

Now add another virtual field using the formula approach, this time for the Address of the pitched firm, pulled all the way from the Pitch object by following the relationship to Meeting and then from Meeting to Pitch:

97. Select Formula and click Next

98. Enter “Address” for Field Label

99. Tab to autotfill Field Name

100. Click the Text option for Formula Return Type and click Next

101. The Advanced Formula tab should still be selected but if not, click it

102. Click the Insert Field button

103. Again, Meeting Attendance > should be highlighted already but if not then click on it

104. In the listbox on the right, scroll down to find Meeting ID > and click to highlight it

105. In the new listbox that opened up, scroll down to find Pitch > and click it

106. Then, in the new (4th) listbox that opened up, click Address

107. The next box that opens says you’ve selected Meeting_ID__r.Pitch__r.Address__c meaning from Meeting Attendance, trace across the Meeting_ID relationship to the corresponding Meeting record and then trace from there, across the Pitch relationship, to the corresponding Pitch record, and retrieve the value of Address from there

108. Click the Insert button

109. Click Next, Next and Save

Now create a tab for Meeting Attendances so you can see them & create a view:

110. Go to Setup > Create > Tabs 

111. Under Custom Object Tabs click New

112. For Object, select Meeting Attendance

113. For Tab Style, click the magnifying glass and select Bridge (appro for a junction object)

114. Click Next, Next and Save

Let’s get a few records in there to see if it works:

115. Click on the Meeting Attendances tab

116. Click New and SF will show you an edit page with just the three fields that are really stored as part of a Meeting Attendance record: Meeting ID, Contact Name and Enthusiasm

117. Click the magnifying glass next to Meeting ID and when you see the Lookup window pop up, enter “M” in the Search... textbox & click the Go! button to see all the Meeting records listed out (b/c they all have Meeting IDs that start with “M”)

118. Click on Mtg‐01, that’s the 10/7/16 meeting, the first one with Julia Chavez 

119. Now you’re back on the edit page with the Meeting ID filled in, so you can click on the magnifying glass next to Contact Name so you can connect this record to Julia’s record in Contacts

120. In the Lookup window that pops up, if you don’t see her name already, just type “Julia” into the Search... textbox and when you see it, click on it

121. Back on the edit page, you can see her name is filled in now so just enter her Enthusiasm rating for that meeting which was “2” and click Save

Back on the record details page for your new record and (hah!) SF is showing the data you just entered for the three fields that are actually stored in the Meeting Attendance object. Ok. Cool.

But amazingly(!), it’s also pulled in Julia’s Email from her Contact record!

And...even more amazingly, it’s pulled in Spray & Pray’s street Address by first connecting to the Mtg‐00 Meeting record and then using the Pitch ID it found there to connect to Pitch and find the right record there. The little Oompa‐Loompas working their magic inside the SF cloud!

Notice, as expected, you can’t double‐click to edit those two “virtual” fields—a little lock icon appears, telling you those aren’t really “here,” they’re just appearing here, virtually.

Now add one more Meeting Attendance record so your reception list will look more realistic:

122. Click on your Meeting Attendances tab & click New

123. Click the magnifying glass by Meeting ID and then, in the popup window, enter “M” for the Search... field and click Go! to see all the Meeting records

124. Select the Mtg‐09 record for the 9/12/16 meeting (it was with a firm in Mountain View)

125. Click the magnifying glass by Contact Name to pop up the Lookup window for Contact

126. Click New to add back the Contact info you deleted earlier for that meeting (Anu Aggarwal)

127. Fill in her First Name, Last Name and enter “[email protected]” for her Email

128. Click Save and you’ll be back on the record edit page with her Contact Name filled in

129. Fill in her Enthusiasm as “2” and click Save

Now let’s see that list of invitees for your reception:

130. Click on your Meeting Attendances tab and then click Create New View

131. For View Name enter “Contact Info for Mountain View Reception”

132. Tab to autofill View Unique Name

133. Under Filter Criteria, pull down the Field menu and select Address

134. Next to that, under Operator, pull down the menu and select Contains

135. Next to that, under Value, enter “Mountain View”

136. Scroll down to the Available Fields list and move Contact Name and Email over to Selected Fields and click Save

Now you’ve got your emailing list. (You can even double‐click one of the addresses to bring up an empty message if you have an email client running on your computer!)

Seems like a lot of work connecting everything up, but SF does most of that for you behind the scenes. And the upside is that the model, with the stuff separated out across entities, gets rid of your data dupe/integrity nightmares. Totally normal(‐ized).

Now database management systems like SF have a name for the computer‐defined version of the model—the “schema” and you can see SF’s own pic of it, to make sure it looks like yours, using the great and powerful Schema Builder:

137. Click on Setup and then, under Build on the left vertical menu, find Develop and just under that, click on Schema Builder to launch SF’s schema building/viewing tool

138. In the left hand column, make sure you’re on the Objects tab and click Clear All to hide all the objects for now—SF comes with a zillion built in objects (overwhelming!)

139. Now check the boxes for your Contact (careful! ‐ NOT ContRact), Pitch, Meeting and Meeting_Attendance objects to unhide them but you may have to zoom and drag them around, etc to see them like below

140. On the lower right, there’s a mini‐map of the currently viewed area and the +/‐ on the right let’s you zoom in and out. You can click‐hold and drag the schema map around or click‐hold on objects to drag them around and the connectors will adjust automatically (see pic below)

1

Older PostsHome

141. A bit of tweaking & you can match that last E/R diagram above pretty darn close except 1) it’s showing those Pitch fields that we hid from the layout but never bothered to delete—no way to hide them here, unfortunately, and 2) Contact has a ton of other built‐in fields and also a relationship to itself that wasn’t in the diagram—SF is just set up that way!

Nice way to visualize your objects & relationships, eh? You can even build your data model this way, dragging & dropping objects onto the schema, adding fields & connecting them up right here instead of all that Next‐ing. (When you’re done playing, click Close on the upper left, just below the Schema Builder label to get back to the regular interface you’re used to.)

<credits roll...finally>

They say it ain’t over it’s over. Well, this one’s over. You’ve got an awesomely normalized data model that still connects everything up but no more duplicate data. Ever. The Dupe‐Monster is truly dead at last. Integrity reigns. Life is good again.

fin

Powered by Blogger.

2