Reilu vuosi sitten kirjoitin Power Appsista, jolla työntekijät voivat kertoa minä päivinä ovat tulossa toimistolle. Ratkaisu on käytössä useassa organisaatiossa. Kun käyttöönoton jälkeen dataa toimistopäivistä alkaa kertymään, on ensimmäinen kysymys aina sama.

Olisiko mahdollista lähettää päivittäin keittiöön yhteenveto siitä, miten paljon henkilöstä on toimistolle tulossa?

Niinpä tietenkin. Meille kertyy sovelluksen käytön myötä tietoa, jota on luontevaa hyödyntää myös muualla. Tällä kertaa arvioitaessa lounasruoan menekkiä.

Mutta miten tämä yhteenveto kerätään SharePoint-listalta keittiölle?

SharePoint-listan sisältö

Miten sovelluksemme tallentaa kävijätiedot SharePoint-listalle? Työntekijät merkkaavat lohkon (osa kerroksesta) tarkkuudella, minne ovat minäkin päivänä tulossa töihin. Sovellus ylläpitää tietoa viikko kerrallaan liukuvan 10 päivän ajan.

Listan sarakkeet

  • Otsikko: Lohkon nimi
  • LocationId: Lohkon tunniste
  • DayID: Mistä päivästä on kyse (1= viikon 1. maanantai, 5 = viikon 1. perjantai 6 = viikon 2. maanantai jne)
  • In: Montako työntekijää on tulossa kyseiseen lohkoon

Kuvasta näemme, että Location 13 -lohkoon on tulossa kuluvan viikon maanantaina (DayId=1) työskentelemään 22 henkilöä. Samana päivänä Location 14 -lohkoon on tulossa 8 henkilöä.

Lohkoja esimerkissämme on 100kpl. Listalla on siis tasan tuhat riviä.

Lasketaan seuraavaksi keittiölle päivittäiset tulijamäärät.

Summien laskeminen flow’ssa

Ensimmäisenä mieleen tulee käydä flow’ssa kaikki rivit läpi ja laskea halutut summat. Tehdään esimerkki, jossa lasketaan ensimmäisen viikon (Day1 – Day5) tulijamäärät yhteen.

Alustetaan kullekin päivälle oma laskuri-muuttuja.

Tämän jälkeen haetaan omissa rinnakkaisissa haaroissaan päiviä 1-5 edustavat rivit (Get items -toiminto).

Lopuksi kussakin haarassa käydään päivän rivit läpi ja lasketaan tulijamäärät yhteen (kunkin lohkon In-arvot). In-kenttä (Field_In) tulee muuttaa erikseen integeriksi, jotta flow on tyytyväinen.

Lopputuloksena tiedämme montako henkilöä päivinä 1-5 on toimistolle tulossa.

Mikä tässä nyt sitten on ongelma?

Ensinnäkin flow’n suoritus kestää 30 sekuntia.

Mikä on tympeää, mutta ei (tällä kertaa) ongelma.

Toisekseen, onhan tämä nyt aika hässäkkä. Laskemme ainoastaan summat viidelle päivälle. Ja flow näyttää jo tältä.

Esimerkissämme lokaatioita on 100. Juuri tekemämme flow’n suoritus käyttää 1012 Power Platform API -kutsua. Se on yli puolet M365-käyttäjän päivän saldosta.

Tähän on oltava järkevämpi tapa.

Summien hakeminen SharePoint-listan näkymästä

Dataversen kanssa summat saa helposti ulos hyödyntämällä toiminnon aggregointia tai FetchXML:ää. SharePointin kanssa näitä vaihtoehtoja ei ole.

Mutta SharePoint-listaan voi luoda monipuolisia näkymiä.

Näkymän luonti

Luodaan listallemme uusi näkymä, jossa rivit on ryhmitelty päivän (DayId) mukaan. Lisäksi näkymässä esitetään toimistolle tulijoiden (In) summat.

Näin meillä on listan rivit ryhmiteltynä päivän mukaan.

Kustakin päivästä löytyy myös tulijoiden yhteenlaskettu määrä.

Näkymän hyödyntäminen flow’ssa

Miten näkymän tietoja voisi hyödyntää flow’ssa? Tietenkin SharePoint REST Apin avulla. Paul Muranan mainiossa blogissa on tästä hyvä esimerkki, josta olen rungon kopioinut.

Kutsua varten tarvitaan

  • listan nimi
  • näkymän id

Voit poimia ne vaikkapa urlista.

Luodaan flow, joka hakee näkymän tiedot Send an HTTP request to SharePoint -toiminnolla. Toiminnon parametrit ovat:

  • Site Address: Sivusto, jonne lista on luotu
  • Method: POST
  • Url: _api/web/lists/GetByTitle(LISTAN_NIMI_TÄHÄN)/RenderListDataAsStream?View=NÄKYMÄN_ID_TÄHÄN
  • Headers: accept application/json;odata=nometa

Toiminto palauttaa JSON:ia, jonka Raw-osiossa on meitä kiinnostava tieto. Kustakin päivästä on oma tietue, jonka sisältä löytyy päiväkohtainen tulijoiden summa (field_In.SUM.agg).

Olemme käytännössä valmiit!

Tulosten muotoilu taulukoksi

Muotoillaan lopuksi yhteenveto summista Create HTML-table -toiminnolla.

Toiminnon käyttämä sisältö (From) on edellisen toiminnon paluuarvon (Body) sisältä löytyvä Row-taulukko.

outputs('Send_an_HTTP_request_to_SharePoint')?['body']?['Row']

DayId saa arvokseen Row-osiosta löytyvän DayId-kentän arvon.

item()?['DayId']

Summana esitetään samasta paikasta löytyvä päivätason summa.

item()?['field_In.SUM.agg']

Ja olemme valmiit.

Pääsimme samaan lopputulokseen kolmella toiminnolla, jotka kuluttavat yhteensä kolme Power Platform palvelupyyntöä.

Näin toteutettuna summien muodostaminen kestää noin 50 millisekuntia.

Viimeistely

Haluamme lähettää päivittäin henkilöstöravintolaan seuraavan viiden päivän kävijäennusteen. Kuluva päivä mukaan lukien.

Eli suodatamme palautetusta näkymästä rivejä pois (Filter array -toiminnolla).

Suodatusehto näyttää hieman monimutkaiselta.

@and(
  greaterOrEquals(
     int(item()?['DayId']),
     dayOfWeek(utcNow())
     ),
  less(
     int(item()?['DayId']),
     add(dayOfWeek(utcNow()),5)
     )
)

Käytännössä sillä suodatetaan rivit joiden

  • DayId on suurempi, kuin kuluvan päivän päivänumero (saadaan DayOfWeek-funktiolla), mutta
  • pienempi kuin kuluvan päivän päivänumero +5

Lopuksi vaihdetaan yhteenvetoon mystisten DayId:n tilalle oikeat päivät.

Tämä tehdään kaavalla

formatDateTime(
   addDays(
      formatDateTime(utcNow(),'yyyy-MM-dd'), 
      sub(
         if(greater(int(item()?['DayId']),5),
            add(int(item()?['DayId']),2),
            int(item()?['DayId'])),
         dayOfWeek(utcNow()))),
   'dd.MM. (ddd)'
)

Jälleen hieman jännittävän näköistä, mutta käytännössä

  • Vähennetään DayId:sta kuluvan päivän numero. Kuluvaan päivän päivämäärään lisätään tuloksen verran päiviä. Näin esimerkiksi keskiviikkona (päivän numero = 3) listamme DayId = 4 arvosta muodostuu torstai (kuluva päivä +1). Vastaavalla kaavalla DayId = 5 muodostuu perjantai.
  • Koukkuna tässä on viikonloppu. Mikäli DayId on suurempi kuin 5 (eli päivä edustaa seuraavaa viikkoa), lisätään kuluvaan päivään ylimääräiset 2 päivää, jotta juoksutus menee oikein

Nyt lauantaina (18.9.) suoritettu flow palautaa seuraavan yhteenvedon.

Lopputuloksen voi lähettää vaikka Teamsiin.

Jossa se näyttää esimerkissämme tältä.

Päivätason summat kaupungeittain

Entä jos toimistoja on eri kaupungeissa (City)?

Tällöin tulisi muodostaa päiväkohtainen yhteenveto kaupungeittain. Muokataan näkymäämme siten että rivit ryhmitellään (Group By) ensisijaisesti päivän (DayId) ja toissijaisesti kaupungin (City) mukaan.

Nyt näkymästämme löytyy päiväkohtaiset tulijamäärät per kaupunki.

SharePointin REST API:n paluuarvo muuttuu hieman. Nyt jokaista päivää kohden on oma tietue per kaupunki. Kaupunkikohtainen tulijamäärä löytyy field_In.SUM.agg2 -kentästä.

Otetaan kaupunki mukaan taulukkoon ja vaihdetaan summaksi field_In.SUM.agg2-kenttä.

Teams-viestimme näyttää tältä.

Kaupunkikohtainen yhteenveto (Distinct flow’ssa Union-komennon avulla)

Haluamme tietenkin lähettää eri toimipisteiden (kaupunkien) keittiöille omat viestinsä.

Tarvitsemme listan kaupungeista. Sellaisen, jossa kukin kaupunki on ainoastaan kerran (distinct)

Luodaan (Select-toiminnolla) summalistasta joukko, jossa on ainoastaan kaupungit. Tulosjoukkona on Espoo, Helsinki, Tampere jne. Kukin 10 kertaa.

Tämän jälkeen tehdään syntyneelle tulosjoukolle Union-toiminto itsensä kanssa.

Ja kas. Meillä on kaupungit nätisti omassa taulukossaan.

[  
  {"City": "Espoo"},  
  {"City": "Helsinki"},
  {"City": "Kankaanpää"},
  {"City": "Tampere"},
  {"City": "Turku"}
]

Käydään taulukko läpi yksi kerrallaan ja muodostetaan kullekin kaupungille oma yhteenveto.

Tätä varten lisäämme suodatus-toiminnon ehtoon mukaan kaupungin.

equals(item()?['Field_City'],items('Apply_to_each')?['City'])

Lopputuloksena meillä on yhteenveto per kaupunki. Yhteenvedot voimme lähettää eri sähköposti-osoitteisiin tai eri teamseihin.