Microsoft Excel-skabeloner – fmcg solutions

Mapping syntax:

Mapping eksempler:

D8256:=D8258_1
D8284:=D8284
Firmaoplysninger:=@Company("name")+" - "+@Company("address")+" - "+@Company("zip")+" "+@Company("city")+" - Telefon "+@Company("phone")+" - CVR "+@Company("vat")
D8183_1:=@If(D8183_1<10;@Round(D8183_1;1);@Round(D8183_1;0))
Indeholdt_i:=@Parent("D8165")

Text

@Text( expression ): Converts input to text
- @Text( 123.0 ) - returns "123"
@FormatNumber( expression; decimals ): Formats number as text with x decimals
- @FormatNumber( 123.0; 0 ) - returns "123"
- @FormatNumber( 123.0; 1) - returns "123,0"
@Uppercase( string ): Convert string to uppercase
- @Uppercase( "Hello" ) - returns "HELLO"
@Lowercase( string ): Convert string to lowercase
- @Lowercase( "Hello" ) - returns "hello"
@Length( string ): Returns the lenght of a string
- @Length( "Hello" ) - returns 5
@Trim( string ): Trims a string and removes whitespace
- @Trim( " Hello World " ) - returns "Hello World"
@Char( string; postion ): Extract a single character from a string. If position is negative, then count from back of the string
- @Char( "Jakob"; 2 ) - returns "a"
- @Char( "Jakob"; -1 ) - returns "b"
- @Char( "Jakob"; 10 ) - returns ""
@Left(string; numberOfChars): extract a substring searching from left to right
@Left(string; delimiter): extract a substring left of delimiter
@Right(string; numberOfChars): extract a substring searching from right to left
@Right(string; delimiter): extract a substring right of delimiter
@Contains(string; substring): checks for substring within the string

Date/time

@Now: Returns the current date/time
@Today: Returns the current date (time is 0:00:00)
@Yesterday: Returns the date before today (@Today-1)
@Tomorrow: Returns the date of tomorrow (@Today+1)
@Date( year; month; day; hour; minute; second ): Creates as new datetime
- @Date( 1968; 22; 10; 18; 30; 00 )
@Adjust( date; year; month; day; hour; minute; second ): Change a date
- @Adjust( @Today; 0;0; 14; 0; 0; 0 ) - returns a date 14 days from now
@DateFromString(string): tries to parse the string as a date using YYYYMMDD or YYYYMMDDHHMMSS as pattern
@DateFromString(string; pattern): tries to parse the string as a date using the supplied pattern
@DateFromWeekNumber(week; year): returns the date of the Monday of the specified week and year
@Created: returns the date/time of when the product unit was created in fmcgProducts

Numbers

@Integer( double ): Convert to int
- @Integer( 3.14 ) - returns 3
@TextToNumber( string ): Convert a string to number using the current language/locale rules
- @TextToNumber( "3,14" ) returns the number 3,14
@Sum( list [; list]): Calculates the sum of all parameters. Both numbers and list of numbers are accepted
- @Sum( 1:2; 3 ) - returns 6
@Round( number; precision ): Rounds a number and returns a number
- @Round( 12.3456; 2 ) - returns 12.35
- @Round( 12.0; 1 ) - returns 12
- @Round( 12.3456; 0 ) - returns 12
- @Round( 12.3456; -1 ) - returns 10
@Min( list [; list] ): Finds the minimum value of all parameters. Both numbers and list of numbers are accepted
- @Min( 1:2; 3 ) - returns 1
@Max( list [; list] ): Finds the maximum value of all parameters. Both numbers and list of numbers are accepted
- @Max( 1:2; 3 ) - returns 3

Lists

@IsUnique( list ): Returns false if list contains duplets
- @IsUnique( "A":"B":"C" ) returns true
- @IsUnique( "B":"B":"C" ) returns false
@Unique( list ): Removes all duplets from the list
@Elements( list ): Returns the number of entries in the list
@Contains( list1; list2 ): Returns true if the two lists shares at least one element
@Replace( source; fromList; toList ): Replace first occurence of values in fromList with values in toList
- @Replace( "A":"B":"C"; "B":"C"; "1":"2" ) - returns "A":"1":"2"
@First( list ): Returns the first element in a list
@Last( list ): Returns the last element in a list
@MemberPosition(list; value): returns position of value in list
@GetMember(list; number): returns specific value from list
@Trim( list ): Trims a list and removes empty entries from the list
- @Trim( "A":"":"B") - returns "A":"B"
@ToList(input; separator): returns a list based on input with the chosen separator

Logical

&: AND
|: OR
@True, @All: Returns true
@False: Returns false
@HasValue( itemname ): Returns true if the item has a value
- @HasValue( "D8165" )
@If( [condition; trueValue;] condition; trueValue; falseValue ): IT-THEN-ELSE statement
- @If( a>b; "Large"; "Small" ) - returns "Large" if a is greater than b

Other

@IsError( expression ): Returns true if the expression returns an error
@IfError( expression; result ): If expression fails, then return result. Same as @If( @IsError( expression ); result; expression )
@Set( itemname; value ): Same as itemname:=value
@Get( itemname ): Gets a value from an item, e.g. @Get( "D8270_" + i )
@GetValue( id; itemname ): Gets a value from the product with the specified id (GTIN.Country)
- @GetValue(D8249_1+"."+D8255; "D8258_1" ); Gets the name of the child product (same as @Child( "D8258_1"))

fmcg functions

@Id( expression ): convert number to an legal id = convert to string, trim for whitespace and removes leading zeroes
- @Id( "0012345.208 " ) - returns "12345.208"
- @Id( D8165+"."+D8255 )
@HasId( id ): Returns true if the database contains a product with the specified id
@Delete: Assigning @Delete (or @Null) to a field wil delete it
@GetEx( itemname ): 'Intelligent' version of @Get. Uses the field list to determine if item is in a grid or should be found on the Base Unit
@GetEx( itemname; language ): Same as @GetEx( itemname ) but with support for language.
- Example: @GetEx("D8181_1"; "en")
@Grid( itemname ): Get all values in a grid column as a list
@GridValue( itemname; searchItem; searchValue ): Returns the value of itemname from the first row where searchItem contains searchValue
- @GridValue( "D8258"; "D8259"; "DA" ): Returns the danish product name from the grid
@GridValues( itemname; searchItem; searchValue ): Returns all the values of itemname where searchItem contains searchValue
@BaseUnitGridValue("itemname"; "searchItem"; searchValue ): Returns from the base unit the value of itemname from the first row where searchItem contains searchValue
- @BaseUnitGridValue( "D8258"; "D8259"; "DA" ): Returns the danish product name from the grid from the base unit
@BaseUnitGridValues("itemname"; "searchItem"; searchValue ): Returns from the base unit all values of itemname from all rows where searchItem contains searchValue
- @BaseUnitGridValue( "D8258"; "D8259"; "DA" ): Returns the danish product name from the grid from the base unit
@ChildGridValue("itemname"; "searchItem"; searchValue ): Returns from the immediate children the value of itemname from the first row where searchItem contains searchValue
@ChildGridValues("itemname"; "searchItem"; searchValue ): Returns from the immediate children all the values of itemname from the all rows where searchItem contains searchValue
@Child( itemname ): Same as @Get but reads from the immediate children products (e.g. from a PALLET the @Child function will read from the CASE)
@BaseUnit( itemname ): Same as @Get but reads from the base unit product (e.g. from a PALLET the @BaseUnit function will read from the BASE_UNIT_OR_EACH)
@Parent( itemname ): Same as @Get but reads from all immediate active parent unit products (e.g. from a BASE_UNIT_OR_EACH contained in a case the @Parent function will read from the immediate CASE and from other immediate parents in which the base unit is contained) and returns a list
@Hierarchy( itemname ): Returns a list with values from all products in the hierarchy (pallet/case/base unit). Order is from highest in the hierarchy to lowest.
@Keyword( fieldname ): translate a field value to keyword value based on the field name only. The field name is used to automatically find the matching keyword.
- @Keyword( "D8255" ) - returns "Denmark" if the value of D8255 is "208"
@KeywordLanguage(fieldName; language): translate a field value to keyword value based on the field name only. The field name is used to automatically find the matching keyword. The value is translated using the selected language.
- @Keyword( "D8255"; "da" ) - returns "Danmark" if the value of D8255 is "208"
@Keyword( value; listname ): translate a keyword alias to a value
- @Keyword( "NACL"; "NutrientTypeCodeList" ) - returns "Salt"
@Keyword( value; listname; language ): same as @Keyword( value; listname ) but with language support
@IsTop(): Returns true if the product unit is at the top of the hierarchi
@AllergenToBold("itemname"; "language"): Converts contents of itemname to bold (<b>word</b>) using the fmcgAllergenFilter keyword document as filter input
@AllergenToUppercase("itemname"; "language"): Converts contents of itemname to upper case using the fmcgAllergenFilter keyword document as filter input
@GPCSegment("gpc brick code"): returns the GPC segment for the brick code
@GPCFamily("gpc brick code"): returns the GPC famliy for the brick code
@GPCClass("gpc brick code"): returns the GPC class for the brick code

Special functions

@Param( name ): Returns a parameter from the Context
@Company( itemname ): Same as @Get - but gets the value from the Company document

Relaterede artikler