Mapping multiple rows to single row, multiple columns

ATTENTION: This forum is no longer active. Please navigate to our new support site at https://support.starfishetl.com/
Viewing 6 posts (of 6 total)
jtakken
User - Author
Post count: 3
#1

I have a dataset that contains multiple phone numbers per contact, each on a different row in the database:

CONTACTID | PHONE TYPE | PHONE NUMBER
1 | Residential | 555-5000
1 | Cell | 555-5001
1 | Cell | 555-5002

What would be the best way to add this logic into Starfish scripting?

Example, it would need to recognize that the Residential phone is the first of that type, and write the value to destination.homephone1. On the second row, it would need to recognize that the Cell number applies to the same contact, it is the first of that type and write the value to destination.mobile1. On the last row, it would need to recognize that again we're dealing with the same contact and a second phone number of type Cell. Since we've already had a mobile number, it would need to write this value to destination.mobile2 et cetera.

Thank you

Comments (6)

Justin Kuehlthau's picture Justin Kuehlthau
Administrator
Post count: 24
#2

Is your destination going to have many of all phone numbers or will the number of phone numbers in the destination be hard coded? Is there a unique id for each of your phone numbers in the origin database?

Log in to post comments
jtakken's picture jtakken
User - Author
Post count: 3
#3

Hi Justin,

For my example I would hard code the number of each phone type to two maximum (home1, home2, cell1, cell2...) though it would be desirable to handle more.

And yes, I do have a preexisting unique identifier for each row in the origin phone table. I should have included that in my initial post: PRIMARY KEY | CONTACTID | PHONE TYPE | PHONE NUMBER

Thanks for your reply,

Jess

Log in to post comments
Aron Hoekstra's picture Aron Hoekstra
Administrator
Post count: 2
#4

I think in this case it would make the most sense to create a separate job to process in the phone numbers. Your origin will look at this phone table.. The first tricky part is assigning it to the correct Contact - for that you'll have to use a SmartLookup function - or, depending on how many contacts you have (if you have a lot) you may want to look at the XRef functions.

The second tricky part is assigning to the correct field based on your type. I'm guessing your stage will be an Update on the Contact table and you just have a static set of fields (home1, home 2, mobile1, mobile 2, etc).. There are a couple different ways you could handle this but both will require a good deal of scripting (on the fly lookups, etc). How comfortable are you with scripting?

Log in to post comments
jtakken's picture jtakken
User - Author
Post count: 3
#5

Hi Aron,

I have a separate update job setup for this, and I am matching on Contact ID.

The problem of course is that both cell numbers exist in different rows in the generic "phonenumber" field.

Currently I have some global variables setup:

  1. public static int oldcontactid = 0;
  2. public static int mcount = 0;
  3. public static int phoneId = 0;

and the following code in the scripted value section of the mobile1/mobile2 fields

mobile1

  1. object ScriptedField()
  2. {	
  3. 	if(Starfish.OriginData[PHONETYPE].Equals("10")) {
  4. 		int currentContactId = int.Parse(Starfish.OriginData[CONTACTID].ToString());
  5. 		int currentPhoneId = int.Parse(Starfish.OriginData[PHONEID].ToString());
  6.  
  7. 		if(oldcontactid == 0) oldcontactid = currentContactId;
  8. 		if(phoneId == 0) phoneId = currentPhoneId;
  9.  
  10. 		if(oldcontactid != currentContactId) {
  11. 			oldcontactid = currentContactId;
  12. 			phoneId = currentPhoneId;
  13. 			mcount = 0;
  14. 		}
  15.  
  16. 		if(mcount == 0) {
  17. 			mcount++;
  18. 			return Starfish.OriginData[PURENUMBER].ToString();
  19. 		}
  20. 		return ""+mcount;
  21.  
  22. 	} else {
  23. 		return "";
  24. 	}
  25. }

mobile2

  1. object ScriptedField()
  2. {	
  3. 	if(Starfish.OriginData[PHONETYPE].Equals("10")) {
  4. 		int currentContactId = int.Parse(Starfish.OriginData[CONTACTID].ToString());
  5. 		int currentPhoneId = int.Parse(Starfish.OriginData[PHONEID].ToString());
  6.  
  7. 		if(oldcontactid == 0) oldcontactid = currentContactId;
  8. 		if(phoneId == 0) phoneId = currentPhoneId;
  9.  
  10. 		if(oldcontactid != currentContactId) {
  11. 			oldcontactid = currentContactId;
  12. 			mcount = 0;
  13. 		}
  14.  
  15. 		if(mcount == 1 && phoneId != currentPhoneId) {
  16. 			mcount++;
  17. 			return Starfish.OriginData[PURENUMBER].ToString();
  18. 		}
  19. 		return ""+mcount;
  20.  
  21. 	} else {
  22. 		return "";
  23. 	}
  24. }

The idea behind this is that the first run through mobile1, it will insert the phone number and increment mcount by 1, and when it runs through the same contact in mobile2 (assuming this is now the second row in the table), it will know this is the second number for this contact because mcount=1. Though I don't know what order Starfish is processing the fields (in other words, if the function in mobile1 is firing before mobile2). If the contactID changes, the counter is reset back to 0.

The result of the above in the preview is:
Row1 - mobile1=555-5555, mobile2=1 (this is mcount)
Row2 - mobile1=444-4444, mobile2=1

Maybe I'm heading down the wrong path here but any suggestions you have would be appreciated!

Jess

Log in to post comments
Aron Hoekstra's picture Aron Hoekstra
Administrator
Post count: 2
#6

Wow very impressive code! But I fear using this method you're going to run into troubles, like the ones you've mention.

What I would do would be this..

declare a variable for each phone you have

  1. public string mobile1 = "";
  2. public string mobile2 = "";

etc..

then

for each scripted field, simply return out the variable..

mobile1:

  1. object ScriptedField()
  2. {
  3.     return mobile1;
  4. }

etc..

On your stage setting, ensure "Skip Blank Writes" is checked.. this is key to making this work, what that means is it will ignore blank/null values and will not attempt to write/overwrite them in the destination..

then you need a Before C# Procedure operation (per row) which sets up your variables for the row.. in reality you'll only be setting one variable and the rest will be blanks..

here's an example:

  1. void CSharpProcedure()
  2. {
  3.      //clear out your variables..
  4.      mobile1="";
  5.      mobile2="";
  6.      //....
  7.  
  8.      //Now check to see if this record's mobile1 is already populated..
  9.      var phone = Starfish.SmartLookup("Contacts", "Mobile1Field", "ContactID = UNIQUEID");
  10.      //change above fields to match your system
  11.  
  12.      if (String.IsNullOrEmpty(phone))
  13.           mobile1 = Starfish.OriginData[PURENUMBER].ToString();
  14.      else
  15.           mobile2 = Starfish.OriginData[PURENUMBER].ToString();
  16.  
  17. }

The code above is untested but is just intended to give an example.. let me know how it works out

Log in to post comments
jtakken's picture jtakken
User - Author
Post count: 3
#7

Thanks Aron, this works well!

Log in to post comments
Viewing 6 posts (of 6 total)

Forum Login

Login or sign up for our forums to connect to the user community.

Reply

You must log in to post.

Not a Member? Register.