ITD NOTIFICATION: Generate a view for NO-PID leads using Power Automate

[jesusitd]

Hi @fedegarza. This is the query for the VIEW of Leads with NO PID.

SELECT
    l.date_entered
    , l.id AS lead_id
    , aos_products.name AS lead_type
    , lc.ap_leadid_c
    , lc.pid_c
    , lc.subid_c
    , l.first_name
    , l.last_name
    , l.status AS 'lead_status'
    , l.phone_home
    , lower(ea.email_address_caps) AS email_address
FROM
    leads l
LEFT JOIN leads_cstm lc ON
    l.id = lc.id_c
LEFT JOIN aos_products ON
    l.lead_form_product_id = aos_products.id
JOIN email_addr_bean_rel eb ON
    l.id = eb.bean_id
    AND eb.bean_module = 'Leads'
    AND eb.primary_address = 1
    AND eb.deleted = 0
JOIN email_addresses ea ON
    eb.email_address_id = ea.id
WHERE
    l.deleted = 0
    AND l.test = 0
    AND NULLIF(
        lc.pid_c, ''
    ) IS NULL
    AND l.date_entered >= DATE_ADD(
        CURDATE(), INTERVAL -7 DAY
    )
ORDER BY
    l.date_entered DESC;

Guess it should be ran in Production to have the 2 replications.

[jesusitd]

Next steps:

• Publish this as a view
• Create notification with Power Automate

[jesusitd]

Done.

https://us.flow.microsoft.com/manage/environments/Default-011d0e09-4e96-4356-a27f-398562aca580/flows/shared/751b8181-e0c6-471d-81f3-941098e45f4a/details