Powershell to fix contact priority / order.

[ADMaster]

I’m not sure how to accomplish this, I’ve tried creating some arrays and nested for each loops, but I’m not getting the results I want.

 

At this point my issues are both syntax and logical process.

I have in a sql database (MIS) contact information for students and a contact priority assigned to each of those contacts.

Most student’s priority 1 contact is a login account that I am filtering out, but that pushes the first real contact to #2. It is not consistent enough to just say priority = priority -1. In some cases what should be a priority 4 may be 6 or 7.

 

Below is a sample set of data, contact ID is a unique id for each contact record, sid is the student ID number, all names and IDs changed of course.

For each student I would like the priority to be 1 2 3 4, but follow the same order as in the database.

 

Thank you

 

[TABLE="width: 0"]
[TR]
[TD]CONTACTID[/TD]
[TD]PRIORITY[/TD]
[TD]SID[/TD]
[TD]LASTNAME[/TD]
[TD]FIRSTNAME[/TD]
[TD]RELATION[/TD]
[/TR]
[TR]
[TD]109[/TD]
[TD]2[/TD]
[TD]390[/TD]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]Father[/TD]
[/TR]
[TR]
[TD]110[/TD]
[TD]3[/TD]
[TD]390[/TD]
[TD]Smith[/TD]
[TD]Jack[/TD]
[TD]Grandfather[/TD]
[/TR]
[TR]
[TD]116[/TD]
[TD]4[/TD]
[TD]390[/TD]
[TD]Smith[/TD]
[TD]Jill[/TD]
[TD]Grandmother[/TD]
[/TR]
[TR]
[TD]115[/TD]
[TD]1[/TD]
[TD]560[/TD]
[TD]Smith[/TD]
[TD]Sarah[/TD]
[TD]Mother[/TD]
[/TR]
[TR]
[TD]110[/TD]
[TD]3[/TD]
[TD]560[/TD]
[TD]Smith[/TD]
[TD]Jack[/TD]
[TD]Grandfather[/TD]
[/TR]
[TR]
[TD]116[/TD]
[TD]4[/TD]
[TD]560[/TD]
[TD]Smith[/TD]
[TD]Jill[/TD]
[TD]Grandmother[/TD]
[/TR]
[TR]
[TD]107[/TD]
[TD]1[/TD]
[TD]921[/TD]
[TD]Smith[/TD]
[TD]Jane[/TD]
[TD]Mother[/TD]
[/TR]
[TR]
[TD]112[/TD]
[TD]2[/TD]
[TD]921[/TD]
[TD]Smith[/TD]
[TD]Sean[/TD]
[TD]Father[/TD]
[/TR]
[TR]
[TD]110[/TD]
[TD]3[/TD]
[TD]921[/TD]
[TD]Smith[/TD]
[TD]Jack[/TD]
[TD]Grandfather[/TD]
[/TR]
[TR]
[TD]115[/TD]
[TD]4[/TD]
[TD]642[/TD]
[TD]Smith[/TD]
[TD]Sarah[/TD]
[TD]Mother[/TD]
[/TR]
[TR]
[TD]110[/TD]
[TD]6[/TD]
[TD]642[/TD]
[TD]Smith[/TD]
[TD]Jack[/TD]
[TD]Grandfather[/TD]
[/TR]
[TR]
[TD]116[/TD]
[TD]7[/TD]
[TD]642[/TD]
[TD]Smith[/TD]
[TD]Jill[/TD]
[TD]Grandmother[/TD]
[/TR]
[/TABLE]

[ADMaster]

I know I posted this late Friday, so here is a Monday bump.

[David44]

To try to clarify what end result you are looking for, are you wanting to flatten out the list of contacts? So if a single student had contacts like this...

1 Mother
3 Father
4 Grandfather
7 Doris down the road

 

You'd want them to end up with contacts like this?

 

1 Mother
2 Father
3 Grandfather
4 Doris down the road

So you want to reindex the contacts? I am assuming this is your MIS database and you don't want to/can't write directly to it? I expect SQL would be the best way to do this if you could write back to the database. If not SQL, how do you have the data (CSV? XML?) and how do you want the data? Will you be giving this data to a human to make the required changes?

[ADMaster]

Yes reindexed as you describe is exactly what I want.

Humans will not have much interaction, but the end result will be a CSV. I have the CSV portion of the code written, I'm just trying to figure out the reindex.

Yes it is my MIS so not writing back.

 

I have 3 purposes in mind and having a predictable index will help.

 

1. Our calling system will use this to map calling priority on who gets called. I could let them sort out the reindex on their end but have use of the reindex results for my own use.

The call system also takes a csv import with student guardian pairs on new lines.

2. My food service system needs contact info, but only needs two. However they are student a both guardians on one line.

I like to say if priority = 1, then fill in contact 1 etc.

3. The same goes for the bus routing software, one line per student and multiple contacts.

 

The results of the sql query are put into an array, if I could do the reindexing direct on the array, perform all the if conditions and then write to csv that would be great.

 

I'm thinking either multiple arrays to temperately hold the data, or multiple for each loops to process it.

[David44]

So your example data should look like this...?

"CONTACTID","PRIORITY","SID","LASTNAME","FIRSTNAME","RELATION"
"109","1","390","Smith","John","Father"
"110","2","390","Smith","Jack","Grandfather"
"116","3","390","Smith","Jill","Grandmother"
"115","1","560","Smith","Sarah","Mother"
"110","2","560","Smith","Jack","Grandfather"
"116","3","560","Smith","Jill","Grandmother"
"115","1","642","Smith","Sarah","Mother"
"110","2","642","Smith","Jack","Grandfather"
"116","3","642","Smith","Jill","Grandmother"
"107","1","921","Smith","Jane","Mother"
"112","2","921","Smith","Sean","Father"
"110","3","921","Smith","Jack","Grandfather"

[David44]

<#Script to create NewContacts.csv file based on the data from contacts.csv but listed in reindexed priority order for each SID.#>$records = Import-Csv -Path .\contacts.csv | Sort-Object -Property SID, PRIORITY;Remove-Item .\NewContacts.csv;$previousPriority = 0;$previousSID = 0;$priority = 1;foreach ($record in $records){    if ($record.SID -eq $previousSID)    {        $priority ++;    }    else    {        $priority = 1;    }    $record.PRIORITY = $priority;    $previousSID = $record.SID;    Export-Csv -InputObject $record -Path NewContacts.csv -NoClobber -Force -NoTypeInformation -Append}

 

EDIT: Turns out I can't post Powershell here as the site is stripping out the carriage returns. Anyone know how to post here so it appears readable?

 

Anyway, the code itself should be good even if it doesn't look very pretty.

Edited October 8, 2018 by David44

[ADMaster]

Brill, here is what I ended up doing. It was so simple after seeing your example, I just needed a second pair of eyes on it.

 

I will now expand the list of properties I put into the csv. This sample doesn't see the results array get populated or guardiansfile defined, that is further up the script.

Thanks

 

[color=#000000][font=Consolas][color=#0000ff]$[/color][color=#001080]CSVArray[/color] = [color=#0000ff]@()[/color]
[color=#0000ff]$[/color][color=#001080]prevsid[/color] = [color=#0000ff]$null[/color]
[color=#0000ff]$[/color][color=#001080]prevpri[/color] = [color=#09885a]0[/color]
[color=#0000ff]$[/color][color=#001080]pri[/color] = [color=#09885a]1[/color]
[color=#af00db]foreach[/color] [color=#0000ff]($[/color][color=#001080]rec[/color] [color=#af00db]in[/color] [color=#0000ff]$[/color][color=#001080]ResultsArray[/color][color=#0000ff])[/color]{
[color=#af00db]if[/color][color=#0000ff]($[/color][color=#001080]rec[/color][color=#795e26].enrollstatus[/color] -ne [color=#09885a]0[/color][color=#0000ff])[/color]{[color=#af00db]continue[/color]}
[color=#af00db]if[/color] [color=#0000ff]($[/color][color=#001080]rec[/color][color=#795e26].sid[/color] -eq [color=#0000ff]$[/color][color=#001080]prevsid[/color] [color=#0000ff])[/color]{[color=#0000ff]$[/color][color=#001080]pri[/color]  ++ } 
[color=#af00db]else[/color] {[color=#0000ff]$[/color][color=#001080]pri[/color] = [color=#09885a]1[/color]}
[color=#0000ff]$[/color][color=#001080]rec[/color][color=#795e26].priority[/color] = [color=#0000ff]$[/color][color=#001080]pri[/color] 
[color=#0000ff]$[/color][color=#001080]prevsid[/color] = [color=#0000ff]$[/color][color=#001080]rec[/color][color=#795e26].sid[/color] 
[color=#0000ff]$[/color][color=#001080]CSVArray[/color] += [color=#0000ff]$[/color][color=#001080]rec[/color]}
[color=#0000ff]$[/color][color=#001080]CSVArray[/color] |[color=#795e26]Select-Object[/color] -Property contactid,priority,sid,lastname,firstname,relation,enrollstatus| [color=#795e26]export-csv[/color] -NoTypeInformation [color=#0000ff]$[/color][color=#001080]guardiansfile[/color] 


[/font][/color]