ECM2 – Rapportages maken en SQL-query’s gebruiken

Geavanceerde query’s

Onze rapportage module heeft veel geavanceerde mogelijkheden en een belangrijke daarvan is het werken met specifiek SQL-query’s. Het werken met specifieke SQL-query’s is alleen nodig voor geavanceerde handelingen en/of complexe relaties tussen modules. Kijk dus eerst goed of het gebruiken van SQL wel nodig is om te bereiken wat je wilt, of dat het met de standaard opties kan, ook bij het maken van uitgebreide rapportages. Bekijk onze handleiding om meer te weten te komen over de standaard functionaliteit of bekijk de handleiding van de ontwikkelaar zelf. Deze helpen jou bij het snel gaan werken met deze module.

Wanneer gebruik je deze functionaliteit?

Je kunt bijvoorbeeld gaan werken met SQL-query’s wanneer je te maken krijgt met relaties tussen velden uit twee modules. Wanneer die niet als standaard relatie zijn gedefinieerd in het CRM, want anders kan je de reguliere functionaliteit gebruiken. De inhoud van zo’n veld is een string van opeenvolgende tekens zoals: “2343565554-66d5-2c0e-f8bc-5abb5e6baca4”. Zo’n string is een unieke ID die verwijst naar een ID in een andere tabel. Vaak heeft zo’n veld een naam: “modulenaam_id” zoals “contact_id” of “opportunity_id”.

Als je zo’n relatie gebruikt in SuiteCRM dan zie je wel een begrijpelijke inhoud, bijvoorbeeld de naam van de record in de gerelateerde module, en niet de ID zoals hierboven. Hoe komt dat? SuiteCRM gebruikt voor jou in dat geval ook een specifieke query en laat aan jouw alleen het resultaat zien, terwijl in de database diezelfde string staat. In wezen doe je dit na in jouw rapportages.

Geavanceerdere voorbeelden zijn het gebruik van MySQL/MariaDB-functies als CONCAT, CONCAT_WS, SUBSTRING, FORMAT, ROUND, et cetera, om het getoonde resultaat te beinvloeden.

Bij het werken met deze uitgebreide functionaliteit moet je wel kennis hebben van SQL-query’s want anders is het lastig om goed query’s samen te stellen en gebruik te maken van deze krachtige functionaliteit. Wil je toch meer, lees je eerst in bij W3schools voor snel starten of ga naar tutorialspoint voor hetzelfde. Let op, het kan zijn dat sommige gerelateerde vragen niet onder de standaard helpdesk support vallen. Afhankelijk van wat de bedoeling is beoordelen wij ook daarop jouw aanvraag en zullen wij dat ook communiceren.

Werken met ID-velden

Maar hoe kunnen wij de velden contact_id of opportunity_id gebruiken en zinvolle informatie laten zien?

• Wanneer je jouw rapportage bewerkt en je ziet in dat overzicht jouw veld staan, in dit geval opportunity_id, noteer eerst met welke module dat veld te maken heeft. In dit geval is dat de module Opportunities.
• Kijk dan van welke veld je informatie wilt hebben. Dat kan zijn de naam van de Opportunity. Binnen het CRM heeft dat veld de naam: name. Als je veldnamen wilt achterhalen gebruik dan de functie Technisch pad onder de Tab Toon velden in de Bewerkmodus. Deze schakel je in door op Pad te klikken in Toon velden en dan op het zwarte pijltje. Een dropdown opent. Klik dan op Columns en kies daaruit Technisch Pad.
• Met deze informatie stel je de volgende Query samen; “SELECT name FROM Opportunities WHERE id=$”. Deze query geeft de opdracht het veld name te selecteren uit de tabel Opportunities.
• Het handige van dit type id-veld “opportunity_id” is dat je ook andere velden uit die module kan selecteren. Dat kan door een ander query samen te stellen zoals “SELECT sales_stage FROM Opportunities WHERE id=$”. Deze query geeft de opdracht het veld sales_stage te selecteren uit de tabel Opportunities. Dus dat kan alleen gedaan worden met een ID-veld en de module waaronder dat ID-veld valt.
• In deze query valty misschien wel op dat er een $-teken staat. Deze variabele verwijst naar ‘de waarde in dit veld van deze record’. Voor een ID-veld is dat dus de ID-string van dat record.
• Waar voer je dat in? De query voer je in bij het veld “Aangepaste Functie” van de record die je wilt bewerken.

Uiteraard is dit puur een technisch voorbeeld en niet in alle gevallen een logische keuze, want je kunt hetzelfde doen zonder deze query te gebruiken en dat veld zelf selecteren in de rapportage. Dit voorbeeld laat echter wel goed zien op welke manier je iets kunt bereiken met query’s. Probeer het echter altijd zo simpel mogelijk te houden.

Geavanceerdere mogelijkheden en betekenis en gebruik van variabelen {t},{tc},{f} of $

Het bovenstaande laat zien hoe je gebruik kan maken van simpele query’s. Nu wil ik een uitgebreider voorbeeld geven voor de gevorderde gebruiker. Ben je dat niet, lees je dan eerst in over het werken met SQL door de bovenstaande linken te volgen over de theorie van SQL met begrijpelijke voorbeelden en door met eenvoudiger query’s te experimenteren.

Je kunt een aangepaste SQL-query in voeren bij het maken van een rapportage in een specifiek veld. Je doet dat in het veld “Aangepaste Functie”. Hoe ziet zo’n uitgebreidere query eruit? Bijvoorbeeld:

• ({t}.amount * {t}.probability) / 100

of

• DATE_FORMAT({tc}.exp_c, ‘%M-%Y’)” , “DATE_FORMAT({t}.{f}, ‘%M-%Y’)” “DATE_FORMAT($, ‘%M-%Y’)” of “CONCAT(‘€’, FORMAT($, 0, ‘en_US’))

We zien hier boven combinaties van functies en variabelen. Je werkt in dat geval met de volgende code op verschillende plaatsen; {t},{tc},{f} of $. Maar wat betekenen deze tekens?

• {t} staat voor de naam van de huidige tabel van de geselecteerde module en deze zien we terug in dat voorbeeld: “{t}.amount * {t}.probability / 100”.
• {tc} staat voor de naam van de custom tabel van de geselecteerde module en de daarbij horende voorbeeld: “DATE_FORMAT({tc}.exp_c, ‘%M-%Y’)” .
• {f} staat voor de naam van het huidige veld: “DATE_FORMAT({t}.{f}, ‘%M-%Y’)”.
• {$} verwijst naar de waarde in het veld van het huidige object. De notatie $ kun je in zulke situatie ook gebruiken als een alternatief voor “{t}.{f}” resp. “{tc}.{f}” waarbij het staat voor de combinatie van tabel + veldnaam van de huidige veld. Je noteert dan “date_format($, ‘%M-%Y’)” in plaats van “date_format({t}.{f}, ‘%M-%Y’)”.

Je kunt ook ingewikkeldere query’s samenstellen, maar houd goed in de gaten of deze kloppen, omdat bij het samenstellen van een dergelijke query snel een fout wordt gemaakt en je een leeg scherm te zien krijgt bij het draaien van de rapportage. Dus eerst zelf goed testen. Een voorbeeld is: “FORMAT((SELECT SUM(amount) FROM opportunities WHERE probability=100 AND id IN (SELECT opportunities_id FROM accounts_opportunities WHERE account_id=(SELECT account_id FROM accounts_opportunities WHERE opportunities_id = {t}.id))), 2)” . Dit is een uitgebreid voorbeeld en laat ook zien dat het samenstellen niet eenvoudig is.

Meerdere emails nodig?

We geven hierbij een laatste voorbeeld: we gaan e-mailadressen ophalen van Personen (Contacts). Wil je in jouw rapportage bijvoorbeeld ook alle niet-primare e-mails van Personen inzichtelijk maken, dan kan dat alleen door gebruik te maken van een specifiek query.

• Voeg het ID-veld van de module Personen toe door dat veld twee keer naar “Toon velden” toe te slepen.
• Verander van het eerste veld de naam “ID” naar “VAR: Contact-ID” en voer bij het veld “Aangepast functie” van dat veld de code “@contact_id := {t}.id” in. Hiermee bouwen we dit veld om naar een variabele die we elders kunnen gebruiken. Om duidelijk te maken dat we dit veld zo gebruiken passen we de naam van dat veld ook aan naar “VAR: Contact-ID”: dat helpt als je deze rapportage later nog een keer gaat berwerken.
• Hernoem dan de tweede “ID” naar “Alle e-mail adressen” en voer bij het veld “Aangepast functie” van dat veld de volgende code in: “SELECT GROUP_CONCAT(ea.email_address) FROM email_addresses AS ea INNER JOIN email_addr_bean_rel AS eabr ON eabr.email_address_id=ea.id AND eabr.deleted=0 AND ea.deleted=0 AND eabr.bean_id=@contact_id ORDER BY eabr.primary_address DESC”.
• Let erop dat de waarde “contact_id” van bean_id=@contact_id overeen komt met de code van veld “VAR CONTACT-ID” met de code “@contact_id := {t}.id” en daar het onderdeel contact_id. Ga je werken met een andere “naam_id” om bijvoorbeeld met gebruikers (users) te gaan werken dat verander je deze twee contact_id’s naar naam_id (user_id) om als nog alle beschikbare e-mailaddressen van de gebruikers te krijgen.
• Op twee manieren kun je de juiste namen van alle velden zien. Alleen de beheerder in het CRM kan via Beheer > Studio > juist module > Velden alle namen achterhalen van de velden waarmee gewerkt gaat worden. De andere manier is door in Google Chrome de toetsen “Ctrl + Shift + C” gezamenlijk in te drukken. Hierdoor opent de “Inspect-modus Select”. Klik daarna eerst op het juiste veld in het CRM om de naam van het veld in de Html-code te zien. Je ziet na selectie de naam van het veld in het blauw oplichten.