Deal_items Teamleader V1: item_subtitle

Vragen
1

Uit de deal_items van Teamleader V1, komt item_subtitle alleen terug bij de eerste regel (item_text) die onder dit kopje valt, maar niet bij de rest. Is dit een fout van Teamleader of gaat dit bij Invantive SQL fout?

Versie: Invantive Data Hub (20.0.134-PROD+1716)

Voorbeeld:

Kop 1
Item 1
Item 2
Item 3

Dit komt terug als:

Kop 1 Item 1
NULL Item 2
NULL Item 3
2

Ik snap de vraagstelling niet goed. Is het mogelijk de JSON toe te voegen zoals beschreven in Collect Native Platform Call Data?

3

Zal ik later doen, maar voor nu ter verduidelijking.
image

De B... bij item_subtitle zou op elke regel moeten staan, maar komt alleen bij het eerste item per subkopje in de deal te staan.

4

Ik begrijp dat het gewenst is. Invantive SQL probeert zo weinig mogelijk correcties te maken in data t.o.v. de bron. Met de JSON die terugkomt is zichtbaar waar de oorzaak ligt.

5 
{
  "title": "titel",
  "responsible_user_id": "123",
  "for": "company",
  "for_id": "123",
  "offerte_nr": "123",
  "quotation_nr": "123",
  "optional_contact_person_id": "",
  "related_project_id": "123",
  "source_id": "123",
  "phase_id": "123",
  "probability": "100",
  "reason_refused": "",
  "total_price_excl_vat": 123,
  "quotation_file_id": "123",
  "entry_date": "123",
  "entry_date_formatted": "05/11/2020 17:03",
  "date_won": "1612192710",
  "date_won_formatted": "01/02/2021 16:18",
  "date_lost": "",
  "date_lost_formatted": "",
  "date_last_activity": "1614806289",
  "date_last_activity_formatted": "03/03/2021 22:18",
  "items": [
    {
      "text": "Artikel1.1",
      "subtitle": "Subtitle1",
      "line_total_excl_vat": 123,
      "line_total_incl_vat": 123,
      "vat_rate": "21",
      "account": "1234",
      "amount": "1",
      "price_per_unit": "123",
      "product_id": "123"
    },
    {
      "text": "Artikel1.2",
      "subtitle": "",
      "line_total_excl_vat": 123,
      "line_total_incl_vat": 123,
      "vat_rate": "21",
      "account": "1234",
      "amount": "1",
      "price_per_unit": "123",
      "product_id": "123"
    },
    {
      "text": "Artikel1.3",
      "subtitle": "",
      "line_total_excl_vat": 123,
      "line_total_incl_vat": 123,
      "vat_rate": "21",
      "account": "1234",
      "amount": "1",
      "price_per_unit": "123",
      "product_id": "123"
    },
    {
      "text": "Artikel1.4",
      "subtitle": "",
      "line_total_excl_vat": 123,
      "line_total_incl_vat": 123,
      "vat_rate": "21",
      "account": "1234",
      "amount": "1",
      "price_per_unit": "123",
      "product_id": "123"
    },
    {
      "text": "Artikel1.5",
      "subtitle": "",
      "line_total_excl_vat": 123,
      "line_total_incl_vat": 123,
      "vat_rate": "21",
      "account": "1234",
      "amount": "1",
      "price_per_unit": "123",
      "product_id": "123"
    },
    {
      "text": "Artikel2.1",
      "subtitle": "Subtitle2",
      "line_total_excl_vat": 123,
      "line_total_incl_vat": 123,
      "vat_rate": "21",
      "account": "1234",
      "amount": "1",
      "price_per_unit": "123",
      "product_id": "123"
    },
    {
      "text": "Artikel2.2",
      "subtitle": "",
      "line_total_excl_vat": 123,
      "line_total_incl_vat": 123,
      "vat_rate": "21",
      "account": "1234",
      "amount": "1",
      "price_per_unit": "123",
      "product_id": "123"
    },
    {
      "text": "Artikel2.3",
      "subtitle": "",
      "line_total_excl_vat": 123,
      "line_total_incl_vat": 123,
      "vat_rate": "21",
      "account": "1234",
      "amount": "1",
      "price_per_unit": "123",
      "product_id": "123"
    },
    {
      "text": "Artikel2.4",
      "subtitle": "",
      "line_total_excl_vat": 123,
      "line_total_incl_vat": 123,
      "vat_rate": "21",
      "account": "1234",
      "amount": "1",
      "price_per_unit": "123",
      "product_id": "123"
    },
    {
      "text": "Artikel2.5",
      "subtitle": "",
      "line_total_excl_vat": 123,
      "line_total_incl_vat": 123,
      "vat_rate": "21",
      "account": "1234",
      "amount": "1",
      "price_per_unit": "123",
      "product_id": "123"
    },
    {
      "text": "Artikel3.1",
      "subtitle": "Subtitle3",
      "line_total_excl_vat": 123,
      "line_total_incl_vat": 123,
      "vat_rate": "21",
      "account": "1234",
      "amount": "1",
      "price_per_unit": "123",
      "product_id": "123"
    },
    {
      "text": "Artikel3.2",
      "subtitle": "",
      "line_total_excl_vat": 123,
      "line_total_incl_vat": 123,
      "vat_rate": "21",
      "account": "1234",
      "amount": "1",
      "price_per_unit": "123",
      "product_id": "123"
    },
    {
      "text": "Artikel3.3",
      "subtitle": "",
      "line_total_excl_vat": 123,
      "line_total_incl_vat": 123,
      "vat_rate": "21",
      "account": "1234",
      "amount": "1",
      "price_per_unit": "123",
      "product_id": "123"
    },
    {
      "text": "Artikel3.4",
      "subtitle": "",
      "line_total_excl_vat": 123,
      "line_total_incl_vat": 123,
      "vat_rate": "21",
      "account": "1234",
      "amount": "1",
      "price_per_unit": "123",
      "product_id": "123"
    },
    {
      "text": "Artikel3.5",
      "subtitle": "",
      "line_total_excl_vat": 123,
      "line_total_incl_vat": 123,
      "vat_rate": "21",
      "account": "1234",
      "amount": "1",
      "price_per_unit": "123",
      "product_id": "123"
    },
    {
      "text": "Artikel4.1",
      "subtitle": "Subtitle4",
      "line_total_excl_vat": 123,
      "line_total_incl_vat": 123,
      "vat_rate": "21",
      "account": "1234",
      "amount": "1",
      "price_per_unit": "123",
      "product_id": "123"
    },
    {
      "text": "Artikel4.2",
      "subtitle": "",
      "line_total_excl_vat": 123,
      "line_total_incl_vat": 123,
      "vat_rate": "21",
      "account": "1234",
      "amount": "1",
      "price_per_unit": "123",
      "product_id": "123"
    },
    {
      "text": "Artikel4.3",
      "subtitle": "",
      "line_total_excl_vat": 123,
      "line_total_incl_vat": 123,
      "vat_rate": "21",
      "account": "1234",
      "amount": "1",
      "price_per_unit": "123",
      "product_id": "123"
    },
    {
      "text": "Artikel5",
      "subtitle": "Subtitle5",
      "line_total_excl_vat": 123,
      "line_total_incl_vat": 123,
      "vat_rate": "21",
      "account": "1234",
      "amount": "1",
      "price_per_unit": "123",
      "product_id": "123"
    },
    {
      "text": "Artikel6",
      "subtitle": "Subtitle6",
      "line_total_excl_vat": 123,
      "line_total_incl_vat": 123,
      "vat_rate": "21",
      "account": "1234",
      "amount": "1",
      "price_per_unit": "123",
      "product_id": "123"
    },
    {
      "text": "Artikel7",
      "subtitle": "Subtitle7",
      "line_total_excl_vat": 123,
      "line_total_incl_vat": 123,
      "vat_rate": "21",
      "account": "1234",
      "amount": "1",
      "price_per_unit": "123",
      "product_id": "123"
    },
    {
      "text": "Artikel8",
      "subtitle": "Subtitle8",
      "line_total_excl_vat": 123,
      "line_total_incl_vat": 123,
      "vat_rate": "21",
      "account": "1234",
      "amount": "1",
      "price_per_unit": "123",
      "product_id": "123"
    },
  ],
  "custom_fields": []
}
6

In de JSON heeft de subtitle alleen een waarde bij de eersten en de rest is leeg.

De representatie is dus conform hoe Teamleader het teruggeeft op de API. Principieel blijft Invantive SQL zoveel mogelijk bij de oorspronkelijke representatie zodat de gegevens zo goed mogelijk herleidbaar zijn.

Mocht een herhaling nodig zijn per regel zoals gevraagd in oorspronkelijke melding, dan raad ik aan om deze logica zelf toe te voegen in Invantive SQL queries of Power BI.