1 / 19

Excel – Friend not foe

Excel – Friend not foe. Excel Friend or Foe of InterAction. Bad things about it. Friendly things to say. It is universal You can email it It is supremely flexible & powerful. People do inappropriate things with it It isn’t a database. My Agenda Using Excel with IA.

yoland
Download Presentation

Excel – Friend not foe

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Excel – Friend not foe

  2. Excel Friend or Foe of InterAction Bad things about it Friendly things to say It is universal You can email it It is supremely flexible & powerful • People do inappropriate things with it • It isn’t a database

  3. My AgendaUsing Excel with IA • Harvesting data using Web Services • How • Problems it can solve.. • Partner Onboarding • Personnel Reconciliations • Harvesting data using Database Services • How • Problems it can solve • Reconciling data • Partner Reporting • Writing data back in to IA using Excel & Web Services • How • Problems it can solve

  4. Useful features • Data • From Web • From Other Sources • From Access • Advantages • Excel stores the data but also knows how to refresh it…. • You can create formulae & formating based on the results

  5. From Web Uses InterAction Web Services

  6. Web – Some Points to Note • Advantages • Simple – you don’t need to understand how IA stores the data • Good for harvesting data • Refreshable • Disadvantages • Duplicate rows • How we have used it: • Partner On-boarding • Comparing Users with Current Employees from HR systems

  7. Partner On-boardingPartner arrives with Excel Loaded in to Outlook Loaded in to IA Process Contacts loaded by DS in to IA folder & working list Merger & promote Synchronise down to my contacts Advantages DS in control; Data quality & no tickets Disadvantages Burden on DS staff • Process • Contacts loaded by secretary in to Outlook • Contacts resolved by secretary • Advantages • Manual Promote • Fee Earner/sec in control • Disadvantages • Assumes Outlook more up to date -> Tickets • Data quality

  8. Partner On-boardingExcel is our friend • Import old contacts in to a sub-contacts folder • Switch on synchs • Compare imported contacts with: • MyContacts/Contacts • Firm List • Comparison at field level

  9. Partner On-boarding tool… • Step 1: Compare Imported Contactswith MyContacts. For missing contacts: • Do they exist in firm list? If so, add Firm List contact to MyContacts • Step 2: Compare individual fields in MyContacts with Imported Contacts – remove differences.

  10. Partner On-boarding tool… Data that is not in the firm list Data that is different to the MyContacts data Data that is different to the firm list

  11. Web – Some Points to Note • Advantages • Simple – you don’t need to understand how IA stores the data • Good for harvesting data • Refreshable • Disadvantages • Duplicate rows • How we have used it: • Partner On-boarding • Comparing Users with Current Employees from HR systems

  12. From the database & other sources

  13. Other SourcesSome Points to Note • Technical • Requires IT to give you read only database connection • Database connection needs to be set up on each PC • Advantages • All InterAction data is available (including record dates etc) • Allows you to ask complex questions • Can use variables (e.g. Today – 6 months) • Can join different databases • Refreshable (without Export) • Can create formulae based on the results • Disadvantages • Need to have an understanding of the database structure • Need to be able to write a little SQL • Need to understand field & data security

  14. Other SourcesHow we have used it • Bounceback Analysis • Using Pivot tables • Joining HR, Active Directory and IA • Discrepancies in job title, start date etc • Partner Reporting • Data from IA in to holding database • Partner report generator in Word • 150 individual PDFs ~ 30 mins

  15. ExcelWriting to InterAction • Native import functions allow • Contacts • Addresses • Additional Fields • Application Collaboration (AC) is required for • List Value items • Relationships • Reminders • The problem with AC • it requires SQL knowledge (normally domain of IT) • need server access (normally domain of IT) • if you don’t have it, it is an additional module (£)

  16. ExcelWriting to InterAction • InterAction Web Services • Add Activity • Add Reminder • Add/Edit Additional Info Field • Add List Value Item • Format • {ServerName}/{MethodName}?{VariableName}={VariableValue} • Useful methods: • methodsreport • additionalfieldsreport

  17. ExcelWriting to InterAction • Template Spreadsheets • Right columns • Validate some of the data • Template builds the URL • Templates for • Add activity • Add relationship • Add additional field • Add reminder …just need to click each link

  18. ExcelWriting to InterAction • Clicking each link is tedious • InterAction Updater Document • Choose spreadsheet • Choose Worksheet • Click Process • Resulting ID is put back in the spreadsheet

  19. Christopher YoungHarborough House Consulting Limited+44 797 9707 991christopher@harboroughhouse.com

More Related