Komplettering till order i "fortnox_stg"

Data sources
1

Hej,

Den som heter ”orders_stg” har jag tänkt använda i Excel, men det saknas några grejer jag gärna hade fått med. Hittar de under ”fortnox_api” så använder de rubrikerna och hoppas du förstår vilka jag menar.

• YourOrderNumber
• TotalVAT
• TermsOfPayment
• Comments
• InvoiceReference
• Gross
• Cancelled
• DeliveryDate
• DeliveryAddress1
• DeliveryAddress2
• DeliveryCity
• DeliveryZipCode
• DeliveryCountry

/Niclas

2

Hej @Niclas_Kettunen!

Tack för din förfrågan. För att gör det så flexibelt som möjligt för dig föreslår jag följande.

Använd datamodellen (SQL-frågan) för “orders_stg”. Lägg i den till och ta bort vilka fält du vill från “fortnox_api.orders”. Kom ihåg att sätta ett “o.” framför det som tillhör ordern och “r.” framför värde från orderraderna.

Visa SQL-frågan för 'orders_stg' 
SELECT
  o.OrgId,
  cs.Name CompanyName,
  o.DocumentNumber,
  o.OrderDate,
  INITCAP(
    -- Makes first letter capital for OurReference
    IF (
      -- Set empty fields to [unknown]
      o.OurReference = "",
      "[unknown]",
      o.OurReference
    )
  ) OurReference,
  Net,
  o.Currency,
  o.sent,
  o.CustomerNumber,
  c.Name CustomerName,
  REGEXP_REPLACE(c.ZipCode, " ", "") ZipCode,
  INITCAP(c.City) City,
  INITCAP(c.Country) Country,
  r.ArticleNumber ArticleNumber,
  INITCAP(IFNULL(a.Description, "[unknown]")) ArticleName,
  r.OrderedQuantity OrderedQuantity,
  r.AccountNumber,
  -- Multiply Article quantity with unit price
  r.DeliveredQuantity * r.price
  -- Convert from foreign currency to SEK
  * o.CurrencyRate *
  -- Apply any % discount rows
  IF (
    DiscountType = "PERCENT",
    1 - r.Discount / 100,
    1
  )
  -- Apply any amount discount rows
  - IF (
    DiscountType = "AMOUNT",
    r.Discount,
    0
  ) price_sek,
  Freight * CurrencyRate Freight,
  r.ContributionValue ContributionValue
FROM
  `enhanza-elt.fortnox_api.orders` o
  -- Flatten OrderRows and join with Invoices
  cross join UNNEST(OrderRows) r
  LEFT JOIN `enhanza-elt.fortnox_api.company_settings` cs ON o.OrgId = cs.OrgId
  LEFT JOIN `enhanza-elt.fortnox_api.customers` c ON o.CustomerNumber = c.CustomerNumber
  AND o.OrgId = c.OrgId
  LEFT JOIN `enhanza-elt.fortnox_api.articles` a ON r.ArticleNumber = a.ArticleNumber
  AND o.OrgId = a.OrgId
WHERE
  -- Filter out cancelled (Makulerade) invoices
  o.cancelled IS NOT TRUE
  -- Set your period
  AND o.OrderDate BETWEEN "2000-01-01"
  AND "2029-12-31"
  -- filter out rows without price
  AND price <> 0
ORDER BY
  CompanyName,
  OrderDate DESC,
  DocumentNumber

Lägg sedan in den nya SQL-koden som en “SQL statement” i MS Excel.

Visa var

enhanza-fortnox-excel-custom-query
enhanza-fortnox-excel-custom-query708×485 18 KB

Funkar det för dig?

Ps. Excel är ju lite speciellt eftersom datan behöver behandlas innan, och levereras som “platt” data. För dig, och övriga som läser detta, skulle jag vilja slå ett slag för vår integration med Microsoft Power BI. Där har vi gjort grovjobbet och erbjuder en kostnadsfri datamodell för all Fortnox-data.

3

Hej,

Tack så mycket! Jag tror det funkar än så länge. Ska testa mig fram under dagen.
Kan du snälla skicka SQL-koden för invoices och supplier_invoices också? Eller kan jag hitta det någonstans? Kolla SQL vid stg-filerna i excel men hittade inget där.

1 Like
4

Hej igen @Niclas_Kettunen!

Jag tror det kommer funka fint för dig. Du hittar alla SQL-koder i Bigquery. Återkom om du kör fast!