Massaal bijwerken relatiecodes in Exact Online naar specifieke nieuwe relatiecodes

Bij gebruik van de querytool is het mogelijk om deze zonder cache te gebruiken op Exact Online.

Zo kan er een relatielijst worden opgevraagd met bijvoorbeeld de query:

select * 
from   ExactOnlineREST.CRM.Accounts

Nu wil ik de relatiecode wijzigen m.b.v. een conversietabel. Hoe krijg ik dit voor elkaar?

Voorbeeld:
In Exact Online heb ik bijvoorbeeld 3 relaties:

Code Naam
1 Relatie A
2 Relatie B
3 Relatie C

Als ik mijn relatiecodes met 10 zou willen verhogen zou ik de volgende query gebruiken:

update ExactOnlineREST.CRM.Accounts
set code = to_number(code)+10

het resultaat is dan:

Code Naam
11 Relatie A
12 Relatie B
13 Relatie C

Nu wil ik een conversie tabel gebruiken waarbij ik de relatie specifieke nummers wil kunnen toekennen, hoe doe ik dat?

Het volgende script is een voorbeeld om relatiecodes in Exact Online massaal bij te werken. Maak altijd VOORAF een backup en test het uitgebreid op een kopie vooraf.

Looptijd is circa 300 ms per bijgewerkte relatie in Exact Online.

local remark Source file in some arbitrary CSV format.
local remark In this case: human company number, old number, name, some audit, new number.

local define CSV_FILE "c:\temp\omnummeringtabel.csv"

--
-- Create a table mapping old account numbers of an
-- Exact Online company to a new (non-used) account number.
--
create or replace table mapping@inmemorystorage
as
select sdn.code division_code
,      csv.*
from   read_file_text('${CSV_FILE}')@Os rft
join   csvtable
       ( passing rft.file_contents
         column delimiter ';' 
         skip   lines 0 /* There is no header row. Change to 1 when there is a header row. */
         columns adm_nummer       integer  position 1 /* Use "next" instead of 1,2,3 on v20.1 */
         ,       oud_nummer       varchar2 position 2
         ,       naam             varchar2 position 3
         ,       datum_aangemaakt datetime position 4
         ,       aangemaakt_door  varchar2 position 5
         ,       nieuw_nummer     varchar2 position 6
       )
       csv
join   systemdivisions sdn
on     sdn.hid = csv.adm_nummer

begin
  --
  -- Select all companies which have an account to be remapped.
  --
  use select distinct division_code from mapping@inmemorystorage;
  --
  -- One-by-one update the accounts.
  --
  -- Precondition: the new numbers are not in use as an old number.
  --
  for r in
  ( select *
    from   MAPPING@InMemoryStorage mpg
    order
    by     mpg.division_code
    ,      mpg.oud_nummer
  )
  loop
    update ExactOnlineREST..Accounts
    set    code     = to_char(r.nieuw_nummer)
    where  code     = to_char(r.oud_nummer)
    and    division        = r.division_code
    and    r.nieuw_nummer != r.oud_nummer
    ;
    if sqlrowcount = 0
    then
      raise_application_error
      ( -20163
      , 'Could not update account from ' 
        || to_char(r.oud_nummer) 
        || ' to ' 
        || to_char(r.nieuw_nummer) 
        || ' in division ' 
        || to_char(r.division_code) 
        || ' because the old number does not exist.'
      );
    end if;
  end loop;
end;

Een bijpassend CSV-bestand ziet er als volgt uit:

199;300005912;;;;400005912
199;300005911;;;;400005911

waarbij in de eerste kolom het administratienummer staat zoals linksboven in Exact Online getoond wordt. De divisioncode wordt er automatisch bijgezocht door de join op SystemDivisions.

Een bijpassend CSV-bestand met kopregel ziet er als volgt uit:

adm_nummer;oud_nummer;;;;nieuw_nummer
199;300005912;;;;400005912
199;300005911;;;;400005911

Een CSV-bestand met kopregel werkt alleen als de skip lines 0 in skip lines 1 veranderd wordt: de CSV-kopregel voor het omnummeren moet overgeslagen worden.

1 Like

Voor het hernummeren van Exact Online relaties via SQL zonder tussenbestand is een scenario beschreven op Relatiecode in Exact Online in bulk wijzigen - #2 door guido.leenders.

Bij het uitvoeren van het script op Exact Online krijg ik de foutmelding:

itgensql375: The value ‘Code_oud; Code_nieuw’ for ‘adm_nummer’ in column 1 and row 1 can not be mapped to a int32.
itgendid048: The value ‘Code_oud; Code_nieuw’ is not a valid integer. Use comma ‘,’ as a thousands separator.
De indeling van de invoertekenreeks is onjuist.

Wat gaat er fout?

Het verwachte CSV-formaat voor het omnummeren bevat geen kopregel. Advies is om de kopregel te verwijderen in de kladblok of notepad++. Het voorbeeld is uitgebreid met skip lines 0 en een voorbeeld CSV zodat duidelijker is dat er geen kopregel verwacht wordt.

Hi Guido,

Vanochtend wat uitgebreider proberen te testen met de aangepaste code, alleen ontstaat er nu een andere foutmelding itgenisr34:

Table ‘MAPPING’ does not exist.

Zie onder de foutmelding en de gebruikte code.

itgenisr034 error

local define CSV_FILE "C:\Export\Testaccounts.csv"

select *
from   read_file_text('${CSV_FILE}')@Os rft

--
-- Create a table mapping old account numbers of an
-- Exact Online company to a new (non-used) account number.
--
create or replace table mapping@inmemorystorage
as
select sdn.code division_code
,      csv.*
from   read_file_text('${CSV_FILE}')@Os rft
join   csvtable
       ( passing rft.file_contents
         column delimiter ';' 
         skip   lines 1
         columns adm_nummer        integer  position 1
         ,       Code_oud          varchar2 position 2
         ,       Code_nieuw        varchar2 position 3
       )
       csv
join   systemdivisions sdn
on     sdn.hid = csv.adm_nummer

begin
  --
  -- Select all companies which have an account to be remapped.
  --
  use select distinct division_code from mapping@inmemorystorage;
  --
  -- One-by-one update the accounts.
  --
  -- Precondition: the new numbers are not in use as an old number.
  --
  for r in
  ( select *
    from   MAPPING@InMemoryStorage mpg
    order
    by     mpg.division_code
    ,      mpg.Code_oud
  )
  loop
    update ExactOnlineREST..Accounts
    set    code     = to_char(r.Code_nieuw)
    where  code     = to_char(r.Code_oud)
    and    division = r.division_code
    ;
    if sqlrowcount = 0
    then
      raise_application_error
      ( -20163
      , 'Could not update account from ' 
        || to_char(r.Code_oud) 
        || ' to ' 
        || to_char(r.Code_nieuw) 
        || ' in division ' 
        || to_char(r.division_code) 
        || ' because the old number does not exist.'
      );
    end if;
  end loop;
end;

De foutmelding itgenisr034 treedt op als een in-memory tabel niet bestaat. Vermoedelijk is het statement met create or replace table mapping@inmemorystorage niet uitgevoerd en is gebruik gemaakt van CtrlEnter om alleen het laadstatement uit te voeren.

Voer alle statements uit (Execute All in menu of toets F5). De verwachting is dat dan wel lukt.

De regels met deze inhoud hebben geen functie lijkt het:

select *
from   read_file_text('${CSV_FILE}')@Os rft

Dank weer! Met F5 was het inderdaad opgelost. Ik gebruikte al die tijd de optie “Uitvoeren”, zie onder. De optie “Execute All” kan ik niet vinden maar met F5 werkt het ook inderdaad.

Optie Uitvoeren in Query Tool

Dank voor aanvulling. Het blijkt dat in de oude Query Editor in 20.0 de knop niet zichtbaar is. Dit zal in volgende release opgelost zijn:

Execute All knop in Query Tool