FIRMS: Create Dataflow for Contacts (Ongoing task)

[jesusitd]

On Firms reports, grab the Contacts table, cleanup the SQL query from unnecessary columns, beautify, and simplify the Power Query if needed:

• Give meaningful names to steps
• Move a step to SQL if it's possible

Then create a Contacts entity inside Tort Vision for Clients dataflow.

Change the Contacts query in every Firm report to point to the dataflow.

[jesusitd]

Created Contacts entity:

let
    Source = MySQL.Database("slave1.sql.intakedesk.com", "suitecrm", [ReturnSingleDatabase=true, OldGuids=true, Query="SELECT#(tab)contacts.id AS `contact_id`,#(lf)#(tab)contacts.first_name,#(lf)#(tab)contacts.last_name,#(lf)        CONCAT_WS(' ', contacts.first_name, contacts.last_name) as name,#(lf)#(tab)contacts.date_entered,#(lf)#(tab)contacts.primary_address_street,#(lf)#(tab)contacts.primary_address_city,#(lf)#(tab)contacts.primary_address_state,#(lf)#(tab)contacts.primary_address_postalcode,#(lf)#(tab)contacts.campaign_id,#(lf)#(tab)contacts.phone_home,#(lf)#(tab)contacts_cstm.client_ssn_c#(lf)FROM #(tab)contacts#(lf)JOIN#(tab)contacts_cstm ON contacts.id=contacts_cstm.id_c#(lf)WHERE#(tab)contacts.deleted=0;", CommandTimeout=#duration(0, 0, 10, 0), CreateNavigationProperties=false])
in
    Source

NEW M CODE:

let
    Source = PowerBI.Dataflows(null),
    #"5b6ed890-8be2-420f-908c-bc78585cd40d" = Source{[workspaceId="5b6ed890-8be2-420f-908c-bc78585cd40d"]}[Data],
    #"8286f25d-cf54-4088-b4e6-0fce9dc74d53" = #"5b6ed890-8be2-420f-908c-bc78585cd40d"{[dataflowId="8286f25d-cf54-4088-b4e6-0fce9dc74d53"]}[Data],
    Contacts = #"8286f25d-cf54-4088-b4e6-0fce9dc74d53"{[entity="Contacts"]}[Data]
in
    Contacts

[jesusitd]

DONE LIST:

• MHM
• Sullivan
• Rosen Injury
• AWKO Campaigns
• Campaigns Master
• Campaigns Master - Case Review
• Bay Advocacy (automatic with AWKO)
• Dugan
• Rosen Injury Campaigns - Paralegal
• Napoli Shkolnik Campaigns
• Napoli Shkolnik Campaigns - Paralegal
• Mass Tort Nexus
• Michael Stanisci Campaigns

DO NOT LIST :) :

• Bailey Greer Campaigns
• Citizens Campaigns
• Dampier Campaigns
• Fears Nachawati Campaigns
• Onder Campaigns
• Seaton Campaigns
• Sheldon Campaigns
• VanDerGinst Campaigns
• Wilentz Campaigns
• Ghia Law Campaigns
• HMG Campaigns
• Martinez Campaigns
• McDonald Worley Campaigns

[jesusitd]

This is going to be an ongoing task.