Vare & pris-import til Visma Business
Oslo, 13. november 2022
Problemstillingen
I Visma Business (VBus) kan nye rader, f.eks. produkter, limes inn på ledige linjer. Dermed er det nokså enkelt å lime inn nye produkter med tilhørende leveringsalternativ og priser. Problemet dukker gjerne opp når det er snakk om å legge inn nye priser på produkter som allerede finnes i VBus. Og det blir litt mer komplisert om man får en vare/pris-liste fra en leverandør som til dels inneholder gamle (tidligere registrerte) og til dels nye produkter. Dette er ikke komplisert å løse for garvede VBus-konsulenter, men kan være en utfordring for brukere som primært jobber med andre problemstillinger. Dette er en alternativ løsning, som baserer seg på Data Model Extension (DME) i VBus.
Kort fortalt
Det er opprettet (med DME) en tabell (som finnes under Tabeller --> Logistikk --> Produkter) med navnet ProduktImport. Denne har da fleste kolonner fra Produkt-tabellen, mange fra Leveringsalternativ, de viktigste fra Pris- og rabattmatrisen, samt Strekkoder. Her kan man registrere (gjerne lime inn fra et regneark) nye produkter, ny informasjon om eksisterende produkter, nye eller endrede leveringsalternativ, samt nye priser.
Handling i tabellen ProduktImport
I tabellen ProduktImport finnes et felt (kolonne) med navnet Handling. Her legges det inn verdier som I, E eller X avhengig av om man ønsker å Importere ett nytt produkt, Endre et eksisterende produkt eller (med X) importere hvis det mangler eller endre hvis det finnes. Velger du I (for Import) og det finnes fra før skjer det ikke noe, bortsett fra et du får en melding om at produktet finnes fra før i kolonnen Feilmelding. Tilsvarende; om du velger E (for Endring) og det ikke finnes fra før, får du melding om dette (fremdeles i kolonnen Feilmelding). Bruk X om du vil importere de som mangler og endre de som finnes fra før.
I tillegg til I, E og X kan du angi hva som skal skje på Lagersaldo-tabellen. Velger du IU (eller evt. XU) opprettes produkt som er unntatt lagerhåndering. Alternativet EU finnes ikke, men se Spesielt om behandlingsmåte nedenfor. Velger du IN, EN eller XN innsettes (hvis det mangler) eller endres (hvis det finnes) en rad i Lagersaldo på Normalt lager. Velger du IA, EA eller XA innsettes eller endres Lagersaldo på alle lagre.
Når import eller endring er gjennomført forsvinner raden fra tabellen ProduktImport. Hvis Handling er blank eller det er gitt en feilmelding (i kolonnen Feilmelding), blir raden værende i tabellen.
Felt som importeres til Produkt
Nesten alle felt på produkt-tabellen kan importeres. Ved endring (Handling Ex eller Xx) endres kun de felt som har verdi i ProduktImport. Se dog Spesielt om behandlingsmåte nedenfor. Følgene felt finnes ikke i tabellen ProduktImport: Malprodukt, Nytt produktnr, Erstatningsprodukt, Tidligere produktnr, Lønnart, Lønnssatsnr, Plantype, Fra, Til og behandlingsmåter som gjelder overstyring av stukturlinjene (Beh. måte 1b, 1c, 2b, 2c, … etc.) samt kolonner som måtte dukke opp i VBus etter versjon 17.01.
Spesielt om behandlingsmåte (på Produkt)
Man kan importere behandlingsmåte for produktet, men ikke overstyring av behandlingsmåten på struktur-linjene (Beh. måte 1b, 1c, 2b, 2c, … etc.). Ved endring kan man legge til nye behandlingsmåter, men ikke ta noen bort. Det er således mulig å importere ny funksjonalitet (behandlingsmåte), men ikke fjerne det som fra før er valgt.
Merk at man ikke kan gjøre oppslag i behandlingsmåtefeltene slik man kan på produkt og ordrelinje.
Automatisk tildeling av Produktnr
Hvis kolonnen Produktnr ikke er utfyllt i tabellen ProduktImport, men Lev.nr og Produktnr hos lev. gjøres det oppslag i tabellen Leveringsalternativ for å finne Produktnr. Hvis produktet ikke er opprettet tidligere kan Produktnr tildeles automatisk. I tabellen Produktnrserie kan det legges opp flere nummer-serier. Det angis antall tegn, neste produktnr og eventuelt et prefiks. Hvis Antall tegn er satt til 0 vil Produktnr hos lev. bli brukt som Produktnr evt. med et prefix. Maksimalt antall tegn er 18 pluss eventuelt prefiks. Dersom det er lagt opp Produktnrserier som vist her og Produktnr hos lev. er 4711, vil nytt Produktnr bli følgende:
1.
4711
2. 01000
3. 00001000
4. HP-00001000
5. HP-4711
Felt som importeres til Leveringsalternativ
På Leveringsalternativ importeres Produktnr, Lev.nr, Produktnr hos lev., Beskrivelse hos lev., Oppr.land, Innkj.enhet, Antall pr. enhet, Øk. innkj.m., Lev. lagersaldo, Adm.tid, Lev.tid, Transp.tid, Kostprispåslag %, Frakt 1, Frakt 2, Frakt 3, Frakt 4, Toll, Frakt beh., Opprinnelsesland, Lev. lagersaldo, leverandørens web.side (for produktet) og Lev.pris. Hvis Lev.pris ikke er oppgitt i ProduktImport, men Innkj.pris, settes Lev.pris til Innkj.pris minus Innkj.rabatt %.
På nye produkter, hvor Adm.tid, Lev.tid og Transp.tid ikke er oppgitt hentes disse fra leverandør.
For eksisterende produkter, men med ny leverandør, legges det til et nytt Leveringsalternativ og de andre (tidligere registrerte) Leveringsalternativ skyves lenger nedover i prioriteringsrekkefølgen (gitt av Sort.sekvn.nr på Leveringsalternativ).
For eksisterende produkter, men med samme leverandør endres Leveringsalternativ med ny informasjon.
Spesielt om Frakt.beh. ved endring av Leveringsalternativ
På samme måte som på Produkt endres bare felt som er oppgitt i ProduktImport-tabellen. Alternativer i Frakt beh. som mangler tilsvarende verdi i Frakt 1, Frakt 2, Frakt 3, Frakt 4 og Toll, blir ignorert.
Felt som importeres til Pris- og rabatt-matrisen
Dersom Fra dato ikke er angitt i ProduktImport-tabellen settes Fra dato til dagens dato. Generelle priser på eksisterende rader i Pris- og rabattmatrisen gis Til dato dagen før Fra dato på den nye raden. Rader i Pris- og rabattmatrisen som har avvikende Enhet, Antall i grunnenhet, Kundenr, Kundeprisgruppe, etc. berøres ikke. Det legges inn ny rad i Pris- og rabattmatrisen med nye priser hvor Fra dato er satt. På de nye radene legges inn (i tillegg til Fra dato) feltene Produktnr, Enhet, Antall i grunnenhet (hvis forskjellig fra 1), Lev.nr, Innkjøpsvalutanr, Innkjøpspris, Innkjøpsrabatt %, Kostvalutanr, Kostpris, Uts.prispåslag %, Valuta, Utsalgspris, Utsalgsrabatt % 1, Bruttopriser og Til dato. Hvis Til dato er tidligere enn Fra dato (evt. tidligere enn dagens dato) blir Til dato ikke importert.
Spesielt om kostpris
Hvis Kostpris ikke er oppgitt i ProduktImport, men Innkjøpspris er oppgitt, blir Kostpris beregnet som innkjøpspris (fratrukket eventuell mva dersom innkjøpspris er angitt som bruttopris) omregnet til lokal valuta minus Innkjøpsrabatt pluss kostprispåslag, frakt og toll. Hvis Lagersaldo skal opprettes, blir Gj.snittlig kostpris beregnet som Kostpris i NOK omregnet til Antall pr. grunnenhet. Er Kostvalutanr oppgitt, men ikke Kostpris blir kostprisen i Pris- og rabattmatrisen omregnet til den oppgitte kostvaluta.
Spesielt om avvikende enhet eller antall pr. enhet på kjøp og salg
Hvis det er angitt ulik Enhet for kjøp (Innkj.enhet) og salg (Stand. enhet) eller ulikt Antall pr. enhet ved kjøp og salg legges det inn to rader i Pris- og rabattmatrisen; én for kjøp og én for salg.
Det samme skjer om Lev.nr er oppgitt; på raden med innkjøpspris blir det lagt på Lev.nr.
Felt som importeres til Lagersaldo
Lagersaldo oppdateres med Normal lev., Øk. innkj.m., Adm.tid, Lev.tid og Transp.tid. Hvis lagersaldo opprettes eller den er tom, oppdateres også Gj.snittlig kostpris med Kostpris i NOK (evt. omregnet til grunnenhet).
Felt som importeres til Strekkode-tabellen
Både Strekkode og DPak strekkode (strekkoden til Innkj.enhet) importeres til Strekkode-tabellen. Sammen med Strekkode legges også Enhet og Antall pr. enhet.
Spesielt om Enhet, Valuta og Land
Feltene Stand. enhet (i Produkt-tabellen), Innkj.enhet og Oppr.land (i Leveringsalternativ); Valuta, Innkjøpsvalutanr og Kostvalutanr (i Pris- og rabattmatrisen) er heltallsfelt. De tilsvarende felt i tabellen ProduktImport er tekstfelt slik at det kan registreres EdiEnhhet eller ISO-kode i stedet for rett nummer for Enhet, Valuta og Land. Dersom EdiEnhhet eller ISO-kode blir registrert i stedet for aktuelt nummer blir det kontrollert om aktuell verdi finnes. Hvis ikke, skjer ingen import og det gis en melding om årsak i kolonnen Feilmelding. I enhets-kolonnene tillates verdier som 1 [Stk] eller [STK]. Dersom [ ikke er første tegn ignoreres alt fra og med [, men om formen er […], ignoreres klammeparantesene.
Spesielt om lagerkonsistens på posteringer
Dersom det i Bokføringstilfeller (i Bedriftsopplysninger) er huket av for Lagerkonsistenskontroll på posteringer og det er angitt en Avg. og bokf.gr., blir det kontrollert at den valgte lagerhåndtering (om produktet er Unntatt lagerhåndtering eller ikke) stemmer med det som er satt på Avgifts- og bokførings-gruppen. Hvis avvik skjer det ingen import, men det gis en forklaring i feltet Feilmelding.
Memo på produkt
Memofilnavn er ett av de felt som kan importeres til produkt. I tillegg er det mulig å importere selve memo-teksten. Da lages det en txt-fil i en undermappe til den mappe hvor memofiler ellers legges. Denne mappen heter ProdMemo og disse memofilene får navn som tilsvarer Produktnr med .txt som ekstension. Dersom et Memofilnavn ikke er oppgitt i ProduktImport, vil Memofilnavn på Produkt vise til dette produktmemo. Skal man lime inn, må man bruke kolonnen Lang beskrivelse. Memotekst kan importeres fra Excel eller registreres ved å dobbeltklikke i feltet.
Import fra Excel
For de fleste vil det være mest praktisk å kopiere fra Excel og lime inn i tabellen ProduktImport. Det er da viktig at rekkefølgen på tabellen stemmer med rekkefølgen i regnearket. Men det er mulig å importere til tabellen ProduktImport direkte fra et regneark. I regnearket må først rad være det samme som SQL-navnet på kolonne til ProduktImport (som med få unntak, se lenger ned i teksten, er det samme som i tabellene som dataene skal til). For å finne SQL-navnene gjør du følgende:
Hent frem de kolonnene du ønsker å importere til tabellen ProduktImport, f.eks. slik;
Velg deretter tekster à SQL:
Dette er de kolonnenavn som må plasseres i første rad i regnearket. Før regnearket skal leses inn må det lagres, og Excel må avsluttes. Bruk vindu Excel import.
Hent frem Excel filnavn (standard
Windows søk). I tabellen Excel Ark
ser du hvilke ark Excel-filen inneholder og hvor mange rader de har, mens Excel kolonne viser hva som står i første rad i
hvert ark, f.eks:
Merk at Filnavn være et område som SQL-server har tilgang til (ikke lokal disk); SQL-server må gis tilgang til de områdene (serverne) som det skal leses fra. Regne-ark med mange ark og/eller mange rader kan ta tid å åpne fordi hvert ark må leses og analyseres med hensyn til om inn-holdet kan importeres. Ark-navn kan ikke inneholde tegnet ' og heller ikke blank; navnet må være ett ord som ikke har et siffer som første tegn. Kolonner uten navn ignoreres, og det står Finnes ikke i Merknad. Det samme skjer med kolonner som ikke finnes i tabellen ProduktImport. Hvis flere kolonner har samme navn, får andre (og tredje etc.) kolonne et tall bak det originale kolonne-navntet. Kolonner som i hovedsak inneholder tekst blir lest som nvarchar eller ntext. For nvarchar blir Max satt til antall tegn i lengste tekst. For ntext er det ingen grense for antall tegn. Kolonner som i hovedsak er tall blir lest som float. I Min og Max står det minste og største verdi som finnes i regnearket. Kolonner som i hovedsak er formattert som en dato i Excel blir lest som datetime.
I VBus er kolonnetypene enten varchar, decimal eller int (i noen tilfelle også tinyint, smallint eller bigint). Det er en fordel om det som skal inn i et tekstfelt i VBus er formattert som tekst i Excel. Tilsvarende med tall; datofelt i VBus må være formattert som dato i Excel. Tekster som er for lange blir kappet ved import. Excel-kolonner med feil format i forhold til VBus blir konvertert om mulig. Kolonner som skal importeres til heltallsfelt kan ikke inneholde desimaltall. Tallene kan heller ikke være større enn kolonnetypen kan holde. Et vanlig heltall (av type int) kan ikke være mer enn 8 siffer. Bittesmå heltall (tinyint) må være mellom 0 og 255. Små heltall (smallint) må være innenfor ±32.767. Vær oppmerksom på at tall over 15 siffer i Excel, ikke blir lagret med mer enn 15 siffers nøyaktighet. Hvis du registrerer tallet 1.234.567.890.123.456 i Excel blir det avrundet til 1.234.567.890.123.450 (siste siffer er ikke signifikant). Dersom du registrerer samme tall i et desimaltalls-felt i VBus får du melding om at Inntil 15-16 siffer godtas. Dette er i praksis først og fremst en problemstilling om man har lange Produktnr, Strekkoder eller GTIN item no (EAN-koder) som i Excel ikke er formattert som tekst. Formatter slike felt som tekst i Excel før data legges inn i disse kolonnene (i Excel).
Sett X (eller noe annet) i kolonnen med overskriften X=Importer og lagre. Da skjer importen til tabellen Produktimport. I kolonnen Merknad står det Importert som en kvittering på at import har skjedd. Du må friske opp tabellen ProduktImport for å se de nye radene. Sett X (eller noe annet) i kolonnen med over-skriften Oppfrisk om du ønsker å lese inn regnearket på nytt (f.eks. fordi det var åpent i Excel ved første forsøk).
Hvis memotekst inneholder linjeskift, vil import fra Excel være en måte å beholde disse.
Dersom regnearket inneholder en kolonne med en overskrift som er et gyldig navn, men som ikke ønskes importert, kan den aktuelle raden slettes fra tabellen Excel kolonne før import. Kontrollkolonner (som Opprettet … og Endret av, … dato, … kl, Redigeringsstatus og Prosessnr) kan ikke importeres. Ei heller kolonnene ProduktID, Feilkode eller Feilmelding som brukes internt av rutinen (verdi tildeles automatisk).
DME: Data Model Extension
Løsningen baserer seg på DME som tidligere var en separat priset komponent i VBus, men som nå er inkludert i hoved-lisensen. Med DME kan det opprettes nye kolonner i eksisterende tabeller, nye tabeller og nye relasjoner (koblinger) mellom kolonner.
For teknisk interesserte
Selve produktimporten er løst med en trigger på tabellen ProduktImport. Det er dermed mulig å bruke rutinen fra andre programmer ved å skrive til denne tabellen. Dersom det skrives i flere omganger (først med insert og deretter update) er det viktig at Handling er blank inntil siste update. Tabellen ProduktImport har VBS- og BIG-navnet ProductImport; SQL-navnet ProdImport. Kolonnene har de samme VBS, BIG- og SQL-navn som tabellene som det importeres til, bortsett fra Antall pr. innkjøpsenhet og DPak strekkode:
|
Kolonne |
Type |
VBS id |
BIG id |
SQL-navn |
Kommentar |
|
ProduktID |
Bigint |
ProductID |
ProductID |
ProductID |
Primærnøkkel, tildeles automatisk i VBus |
|
Handling |
Varchar(2) |
Action |
Action |
Action |
|
|
Produktnrserie |
Int |
ProductNoSeries |
ProductNoSeries |
ProdNoSer |
Se tabellen Produktnrserie |
|
Memotekst |
Varchar(max) |
MemoText |
MemoText |
MemoText |
Teksten i ProduktMemo |
|
Lang beskrivelse |
Varchar(1000) |
LongDescription |
LongDescription |
LongDescr |
Teksten i ProduktMemo |
|
Antall pr. innkj.enhet |
Descimal(28,6) |
PurchaseQuantityPerUnit |
PurchaseQuantityPerUnit |
PUnRt |
|
|
Dpak strekkode |
Varchar(52) |
PurchaseBarcode |
PurchaseBarcode |
PSCd |
|
|
Feilkode |
Int |
ErrCd |
ErrCd |
ErrCd |
Skrivebeskyttet i VBus |
|
Feilmelding |
Varchar(80) |
ErrorMessage |
ErrorMessage |
ErrMsg |
Skrivebeskyttet i VBus |
Tabellen Produktnrserie har VBS- og BIG-navnet ProductNoSeries, SQL-navnet ProdNoSer og har følgende felt:
|
Kolonne |
Type |
VBS id |
BIG id |
SQL-navn |
Kommentar |
|
Produktnrserie |
Int |
ProductNoSeries |
ProductNoSeries |
ProdNoSer |
Primærnøkkel |
|
Neste produktnr |
Bigint |
NextProductNo |
NextProductNo |
NxtProdNo |
Ignoreres om Antall tegn er null |
|
Antall tegn |
Tinyint |
Length |
Length |
Length |
Verdi over 18 ignoreres |
|
Prefix |
Varchar(8) |
Prefix |
Prefix |
Prefix |
|
Merk at når Antall tegn er null brukes Produktnr hos lev. som eget Produktnr, evt. med Prefiks.
Feilkode i tabellen ProduktImport kan en verdi som er summen av følgende verdier:
1 Produktnr finnes fra før. Velg annen handling.
2 Både ProduktNr og ProduktNrSerie mangler.
4 ProduktnrSerie finnes ikke.
8 Produktnr hos lev mangler. Velg annen serie.
16 Produktnr hos lev kan ikke brukes som eget produktnr. Velg annen serie.
32 ProduktnrSerie er full.
64 Produktnr finnes ikke fra før. Velg annen handling.
128 Strekkode brukes på annet produkt.
256 DPak strekkode brukes på annet produkt. Må være ulik Strekkode.
512 Valuta er ikke gyldig; hverken blank, ISO eller Valutanr.
1024 Innkjøpsvaluta er ikke gyldig; hverken blank, ISO eller Valutanr.
2048 Kostvaluta er ikke gyldig; hverken blank, ISO eller Valutanr.
4096 Standard enhet er ikke gyldig; hverken blank, EDI-enhet eller Enhetnr.
8192 Innkjøpsenhet er ikke gyldig; hverken blank, EDI-enhet eller Enhetnr.
16384 Opprinnelsesland er ikke gyldig; hverken blank, ISO eller Valutanr.
32768 Lev.nr finnes ikke.
65536 A&B-gruppe finnes ikke i tabellen Avg. og bokf.gr.
131072 Ugyldig kombinasjon av A&B-gruppe og lagerhåndtering.
262144 Velg gyldig handling (E, I, X, EA, IA, XA, EN, IN, XN, IU eller XU).
524288 Tabell låst av annen prosess.
Feilkode kan romme flere feil, mens i Feilmelding vises bare den groveste feilen. Så lenge Feilkode har annen verdi enn 0 vil det ikke skje noen import. Og så lenge Handling er blank, skjer det heller ingen import. Den siste feilen forekommer bare alene, og teksten viser hvilken tabell som er låst og av hvem. Du kan dobbelt-klikke i Feilkode for å se alle feilene i klartekst.
Rader som registreres (eller limes inn) i VBus er gjenstand for alminnelig inputkontroll. Eventuell bruk av Obligatoriske teksttyper fungerer på ProduktImport på samme måte som ellers i VBus. Skriver man direkte til tabellen må man selv ta ansvaret for at verdiene er gyldige (se dog det som er skrevet om land, enhet og valuta).
Excel leses med SQL-prosedyren OPENROWSET.
Tekniske forutsetninger
For å kunne lage memofiler må det på SQL-server åpnes for Ole Automation Procedures. Det må opprettes en mappe med navnet ProdMemo under hver firma-mappe for memofiler.
For å kunne lese Excel må Microsoft Access Database Engine Redistributable installeres på SQL-server. Dette er en komponent som åpner for trafikk mellom Officeprodukter og andre programmer (i dette tilfelle SQL-server). I tillegg må det åpnes for Ole Automation Procedures, Ad Hoc Distributed Queries og bruk av AllowInProcess samt DynamicParameters for ACE.OLEDB. Og SQL-server må gis tilgang til å lese filer på de servere hvor Excel-filene tenkes plassert.
Det er ikke nødvendig å installere disse komponentene om man bare skal kopiere fra Excel (med Ctrl-c) og lime inn i tabellen ProduktImport (med Ctrl-v).
Noen ord om ytelse/tidsforbruk
Hvis det tar 15 minutter å lime 35.000 rader inn 45 kolonner i tabellen ProduktImport, kan det ta 10 minutter å innsette nye produkter hvis de ikke finnes fra før og 20 minutter å endre informasjon om alle finnes fra før. Hvor lang tid det tar å lime inn rader i tabellen ProduktImport, avhenger av hvilke kolonner det limes inn i: Kolonner som valideres av VBus, som Avg. og bokf.gr., Normalt lager, Ansvarlig, Lev.nr, etc. tar lengre tid å lime inn enn andre felt. Tiden som går til å innsette nye rader eller endre eksisterende rader er upåvirket av hvor mange kolonner som har verdi i ProduktImport.
Demonstrasjon?
Ta kontakt om du ønsker en demonstrasjon. Ta gjerne med et eksempel på det du har behov for å importere, enten det er et Excel-ark, CSV-fil eller hva det måtte være som er din kilde.
Resten av min blogg kan du lese her: frode.antun.no/VBus/blogg
Frode Antun
Løsningsrådgiver
amesto
Solutions
m: + 47 911 46 751
a: Smeltedigelen 1, 0195 Oslo