rex0220 kintone formula plugin

--- title: rex0220 kintone formula plugin tags: kintone, plug-in, plugin, rex0220 --- # rex0220 kintone formula plugin Adding this plugin to your kintone app allows you to calculate value depending on the formula in the given plug-in setting. You can process Conditional Total Calculation etc, which is not in the standard function, just by specifying the calculation formula. Complex conditions can be specified by a specification method like Excel calculation formula. This plug-in is on sale at [rex0220e kintone plugin](https://rex0220e.stores.jp/) Calculation expression Example: In the calculation formula, specify field codes, functions, and so on. - Record_Number & "-" & DATE_FORMAT(Date1, "YYYY-MM-DD") - SWITCH(Drop_Down,"Accounting","General","Sales","Others") - SUMIF(Drop_Down0==="Train(one way)", Number) + SUMIF(Drop_Down0==="Train(round trip)", Number) - SUMIF(IN(Drop_Down0, "Train(one way)", "Train(round trip)"), Number) The operating environment is PC and smartphone. # formula plug-in setting example ![formula plug-in setting example](https://i.imgur.com/G9hX9Wb.png) # Edit screen example ![Edit screen example](https://i.imgur.com/8gg3gmV.png) # Smartphone screen This is an example of calculation processing at status transition. ![Smartphone screen](https://i.imgur.com/EHiM0Dc.png) # Function introduction - Concatenate characters - Concatenate record number, status to character item - Change the character string to be set with the value of another item - Calculation - Conditionally sum the numerical values of the sub table - Even if there are items not yet entered in the sub table, totaling is done # Constant - Character: enclose it with "". "CORPORATION", "individual" - Numeric: Describe the number as it is 0, 1234, 123.456 # Operator - Character concatenation: & - Arithmetic: +, -, *, /,%, **, ^ - Compare: =, ==, ===,! ===, \>, <,> =, <=,! =, <> - Logic: &&, || # Function As for the function, "(" is written without a space. If there is space, it is treated as fieldcode. FunctionName(parameter) OK: CEIL(number, 1) NG: CEIL (number, 1) # Function list - CEIL: Round up - CEIL(fieldcode) Round up decimal places - CEIL(fieldcode, decimal point position) - Example: CEIL(number, 1) - COUNT: Numerical count - COUNT(fieldcode [, fieldcode ...]) - Example: COUNT(number) - COUNTA: Non-blank count - COUNTA(fieldcode [, fieldcode ...]) - Example: COUNTA(Text) - COUNTIF: Conditional numeric count - COUNTIF(condition, fieldcode [, fieldcode ...]) - Example: COUNTIF(Text == "A", number) - COUNTROW: Number of rows in the sub table - COUNTROW(fieldcode) - Example: COUNTROW(Table) - ROWNO: Subtable row position being processed - ROWNO(fieldcode) - Example: ROWNO(Table) - FLOOR: devaluation - FLOOR(fieldcode) Round down the decimal point - FLOOR(fieldcode, decimal point position) - Example: FLOOR(Number, 1) - IF: condition - IF(condition, fieldcode [, fieldcode]) - Example: IF(string == "A", Number, Number1) - IFS: multiple conditions - IFS(condition, fieldcode [, condition, fieldcode [, fieldcode]] [, fieldcode]...) - Example IFS(string == "A", Number, Text1 == "B", Number1) - IN: Check for the existence of values ex. - IN(fieldcode, string [, string ...]) - Example: IN(Text, "ABC", "DEF", "GHI") - INT: integer - INT(fieldcode) - Example: INT(Number / 2) - ROUND: Rounded - ROUND(fieldcode) Round off decimal places - ROUND(fieldcode, decimal point position) - Example: ROUND(Number, 1) - AVE: average value - AVE(fieldcode [, fieldcode ...]) - Example: AVE(Number) - Example: AVE(Number, Number_0, Number_1) - AVEIF: conditional average - AVEIF(condition, fieldcode [, fieldcode ...]) - Example: AVEIF(Text == "A", Number) - MAX: Maximum value - MAX(fieldcode [, fieldcode ...]) - Example: MAX(Number) - Example: MAX(Number, Number_0, Number_1) - MAXIF: conditional maximum value - MAXIF (condition, fieldcode [, fieldcode ...]) - Example MAXIF (string == "A", number) - MIN: minimum value - MIN(fieldcode [, fieldcode ...]) - Example: MIN(Number) - Example: MIN(Number, Number_0, Number_1) - MINIF: conditional minimum value - MINIF(condition, fieldcode [, fieldcode ...]) - Example: MINIF(Text == "A", Number) - SUM： Total - SUM(fieldcode[,fieldcode...]) - Example: SUM(Number) - Example: SUM(Number, Number_0, Number_1) - SUMIF： conditional sum - SUMIF(condition,fieldcode[,fieldcode...]) - Example: SUMIF(Text=="A", Bumber) - SWITCH： Return specified value with multiple conditions - SWITCH(fieldcode, matched value, value, [matched value, value [,...]], default value) - Example: SWITCH(Text,"A", Number1, "B", Number2, Number3) - FIRST： First valid value - FIRST(fieldcode[,fieldcode...]) - Example: FIRST(Text) - Example: FIRST(Number) - FIRSTIF： First conditional valid value - FIRSTIF(condition,fieldcode[,fieldcode...]) - Example: FIRSTIF(Text=="A", Text1) - Example: FIRSTIF(Text=="A", Number) - LAST： Last valid value - LAST(fieldcode[,fieldcode...]) - Example: LAST(Text) - Example: LAST(Number) - LASTIF： Last conditional valid value - LASTIF(condition,fieldcode[,fieldcode...]) - Example: LASTIF(Text=="A", Text1) - Example: LASTIF(Text=="A", Number) - SUBTABLE：　Value of subtable designation line (0-) - SUBTABLE(fieldcode, Specified row) - Example: SUBTABLE(Text, 0) ## Date function - DATE_DIFF: Date difference function - DATE_DIFF(fieldcode|"TODAY" ,fieldcode|"TODAY" ,type) - type : "years", "months", "weeks", "days", "hours", "minutes", "seconds" - Example: DATE_DIFF(Date, "TODAY", "years") - DATE_FORMAT: Date format - DATE_FORMAT(fieldcode [,format [,timezone]]) - DATE_FORMAT("TODAY"[,format [,timezone]]) - Example: DATE_FORMAT(Date, "YYYY-MM-DD") - DATE_ADD: Date addition - DATE_ADD(fieldcode|"TODAY", num, type, format) - type : "year", "month", "week", "day", "hour", "minute", "second" - Example: DATE_ADD(Date, 1, "month", "YYYY-MM-DD") - DATE_STARTOF: Start of date - DATE_STARTOF(fieldcode|"TODAY" , type, format) - Example: DATE_STARTOF(Date, "month", "YYYY-MM-DD") - DATE_ENDOF: End of date - DATE_ENDOF(fieldcode|"TODAY" , type, format) - Example: DATE_ENDOF(Date, "month", "YYYY-MM-DD") - DURATION: Duration function (convert to milliseconds) - DURATION(fieldcode [, type]) - type : "years", "months", "days", "hours", "minutes", "seconds" - Example: DURATION(Time) - Example: DURATION(Number, "hours") - DURATION_AS: Duration function (converts to specified unit) - DURATION_AS(fieldcode, type) - type : "years", "months", "days", "hours", "minutes", "seconds" - Example: DURATION_AS(Time, "hours") - DURATION_GET: Duration function (Retrieve the numerical value of the specified unit) - DURATION_GET(fieldcode, type) - type : "years", "months", "days", "hours", "minutes", "seconds" - Example: DURATION_GET(Time, "hours") - DURATION_FORMAT: Duration format (Convert to specified format) - DURATION_FORMAT(fieldcode, format) - format: Specify a combination of formats - "$": If the period is negative,"-" - "#Y", "#M", "#D", "#H", "#m", "#s": Convert to designated unit (including decimal) - "%Y", "%M", "%D", "%H", "%m", "%s": Convert to designated unit (not including decimal) - "HH", "MM", "SS": Acquire designated unit (2 digit display) - Example: DURATION_FORMAT(Time, "HH:mm") - Example: DURATION_FORMAT(Time, "%Hhours #mminutes") ## String function - FIND： Return character position by searching character string - FIND(fieldcode, string) - Example: FIND(Text, "A") - SEARCH： Search character string (regular expression) and return character position - SEARCH(fieldcode, pattern[, flags]) - flag: "g", "i", "m", "u", "y" - Example: SEARCH(Text, "A.b", "i") - TEST： Returns existence (true, false) of characters in character string search - TEST(fieldcode, string [, string...]) - Example: TEST(Text, "A", "B") - TESTRE： Returns existence (true, false) of characters in character string search (regular expression) - TESTRE(fieldcode, pattern[, pattern]) - Example: TESTRE(Text, "A.b", "AAX", "Hello") - TEXTJOIN： Join strings - TEXTJOIN(delimiter, ignore_empty, fieldcode [, fieldcode...]) - ignore_empty: 1, 0 - Example: TEXTJOIN("," , 1, Text1, "A.b", Text2) - REPLACE： String replace - REPLACE(fieldcode, pattern, string) - Example: REPLACE(Text, "AB", "XYZ") - REPLACERE： String replace(regular expression) - REPLACERE(fieldcode, pattern[, flags], string) - flag: "g", "i", "m", "u", "y" - Example: REPLACERE(Text, "A.b", "i", "XYZ") - UPPER： Convert to upper case - UPPER(fieldcode) - Example: UPPER(Text) - LOWER： Convert to lower case - LOWER(fieldcode) - Example: LOWER(Text) - LEFT： Left of string - LEFT(fieldcode, length) - Example: LEFT(Text, 3) - MID： Obtained from specified position of character string - MID(fieldcode, start, length) - Example: MID(Text, 2, 3) - RIGHT： Right of string - RIGHT(fieldcode, length) - Example: RIGHT(Text, 3) - SLICE： Cut out a character string（start, end） - SLICE(fieldcode, start, end) - Example: SLICE(Text, 2, 3) - SUBSTR： Cut out a character string（start, length） - SUBSTR(fieldcode, start, length) - Example: SUBSTR(Text, 2, 3) - SUBSTRING： Cut out a character string（start, end） - SUBSTRING(fieldcode, start, end) - Example: SUBSTRING(Text, 2, 3) - LENGTH： String length - LENGTH(fieldcode) - Example: LENGTH(Text) ## Logical function - BOOL： Boolean conversion - BOOL(fieldcode) - Example: BOOL(Text) - NOT： Not - NOT(fieldcode) - Example: NOT(Text) - AND： And - AND(condition[,condition...]) - Example: AND(Text=="A", Text1=="B", Text2=="C") - OR： Or - OR(condition[,condition...]) - Example: OR(Text=="A", Text1=="B", Text2=="C") - XOR： Exclusive or - XOR(condition[,condition...]) - Example: XOR(Text=="A", Text1=="B", Text2=="C") ## Login information - LOGIN_NAME： Login Name - LOGIN_NAME() - Example: LOGIN_NAME() - LOGIN_CODE： Login code - LOGIN_CODE() - Example: LOGIN_CODE() - LOGIN_EMAIL： Login mail address - LOGIN_EMAIL() - Example: LOGIN_EMAIL() # How to writing formula ## Field code setting Since the field code is used in the calculation formula, it becomes very difficult to understand with the initial setting. First let's set field codes according to labels. There are characters that can not be used in field codes, so change them accordingly. - Default field code:　Number * Number_0 - After changing according to label:　UnitPrice * Quantity *[kintone field code setting plug-in](http://qiita.com/rex0220/items/e2a475c8097740f94254) You can fit the field code to the label.　 ## Numerical calculation - Formula for calculating the amount: UnitPrice * Quantity - consumption tax:　FLOOR(UnitPrice * Quantity * 0.8) - Subtable calculation: SUM(Amount) - Conditional sum of subtable:　SUMIF(IN(transportation, "Train (one way)", "Train (round trip)"), Amount) - Conditional average of subtable:　AVEIF(IN(transportation, "Train (one way)", "Train (round trip)"), Amount) ![](https://i.imgur.com/tQB9L7K.png) ## record number You can specify the field code and \$id. \$id can be used without placing a record number on the form. - Serial number by ID：　"A" & RIGHT(10000000 + \$id, 5) - Serial number by ID２： IF(\$id, "A" & RIGHT(10000000 + \$id, 5), "(Auto filled)") # Import plugin - "kintone Administration > Plugin" Import plug-ins. ![](https://i.imgur.com/0QMRa6I.png) - "App setting > Plugins" Add plugin. ![](https://i.imgur.com/ylFrHRn.png) # Plugin setting - Select the item to use the calculation formula in the target application and set the calculation formula. - Use the filter function to narrow down the display items. - Click on the grid's label column to sort in that order. - Click "Save". *When changing the form, please make this plug-in setting after reflecting the form change in the production environment. ![](https://i.imgur.com/ZqwbRgA.png) You can specify expression "new line" in the calculation formula to make the expression easy to see. "new line" is ignored at run time. ## Filter function - If you enter a keyword in the filter input field, only the corresponding item will be displayed. - The keyword is displayed in yellow. - You can also select extraction conditions and target fields with dropdown. - Reset the filter with the reset button. - Even if it is redisplayed after filtering, display order does not return. Use the sort function as necessary. ![](https://i.imgur.com/XP9vpJ2.png) ## Sort function - Click on the header part of the grid to sort. - Click again to display in reverse order. ![Uploading file..._n8o4jhsn5]() ![](https://i.imgur.com/8fWne0G.png) ## Function input support If you enter "?" In the edit area, the list of possible functions is displayed. When you click on a function, the function name is entered in the editing area. ![](https://i.imgur.com/GMjduwr.png) ## Field code input support When you enter ":" (colon) in the edit area, the item list (item name: field code) is displayed. When you click an item, the field code is entered in the editing area. ![](https://i.imgur.com/uEYrkxC.png) # On conditional expressions for name setting for each language When setting the name for each language in the value of the item, specify the condition corresponding to each language in the conditional expression. - Dropdown, radio button, check box, multiple selection, status Name setting example for each language of check box We have set names in English, Japanese and Chinese respectively <img width="1042" alt="2017-08-26_14h13_52.png" src="https://qiita-image-store.s3.amazonaws.com/0/100572/b13b7ab5-e4f0-2ee8-8aaf-b3608bf95ee7.png"> Since the displayed value varies depending on the user's setting language, the conditional expression also corresponds. The conditional expression when sample1 is selected is as follows. IN (checkbox, "sample1", "sample1", "goods1") # Error check If you specify something that is not a constant, operator, function name, or field code in the calculation formula, it displays an error. ![](https://i.imgur.com/THjneEM.png) # About circular reference error In the calculation formula, please do not refer to each other between items. Circular references between two simple items will cause an error in the check at "save" Setting circular references via multiple items will result in an infinite loop when changing records. However, if analysis processing of plug-in is performed more than 10,000 times on one screen at the time of editing, processing is interrupted as an error. ![](https://i.imgur.com/oiNRnQ8.png) ![](https://i.imgur.com/dfWux7I.png) # Update existing record You can update existing records with calculation formulas. The target is all records. - Click the "Update existing record" button. - The number of target records is displayed. - Click the "Update Record" button to start the update process. *After reflecting the form change in the production environment, please make an existing record change. Please back up your data before changing. ![](https://i.imgur.com/t2WIHLy.png) # Download plugin settings - Plug-in settings can be downloaded. - If you change the formula, the setting after the change is the target. - Browser IE is not subject to download behavior of plug-in settings. ![](https://i.imgur.com/G8JTh5G.png) # Upload plugin settings - Plugin settings can be uploaded. - Set the calculation formula from the setting file with the field code as the key. - Browser IE is not subject to plug-in setting upload operation. ![](https://i.imgur.com/csGrKyj.png) - Check the plug-in configuration file information and set it. ![](https://i.imgur.com/uLsi2Dp.png) # Notes In the case of applications already customized with JavaScript, processing may conflict and an error may occur. In that case, please stop using the plug-in with the corresponding application. # Calculation formula setting example ## Aggregate the amount of the table by item It is an example of tabulating the amount items in the table for each subject in the same row and setting it as the total item. - Conditional aggregation in tables Conditional aggregation with SUMIF function. - SUMIF (Subject == "Ticket", amount) ## Retrieve table field value Calculation formula example for acquiring the latest correspondence content in customer correspondence history etc. Determines and obtains the last line in the table or the latest date line in the table. - Last line of table In the LASTIF function, get the last line containing the value. - DATE_FORMAT(LASTIF(corresponding_date, corresponding_date), "YYYY-MM-DD") - LASTIF(Corresponding_date, corresponding_content) - Maximum value of the table Get the maximum date in the table and retrieve the items in that row with the LASTIF function. - DATE_FORMAT(MAX(corresponding_date), "YYYY-MM-DD") - LASTIF(corresponding_date==max_date, corresponding_content) ## Time summary setting Example of setting up the working hours. - Calculate the time items in the table and tabulate the working hours - Total working hours DURATION_FORMAT( SUMIF(AND(End_Time,Start_Time,Break_Time), DURATION(終了)-DURATION(Start_Time)-DURATION(Break_Time,"minutes")), "$%Hhours @mminutes") - Calculate working hours from time items in the table. - Working hours IF(AND(End_Time,Start_Time,Break_Time), DURATION_FORMAT( DURATION(End_Time)-DURATION(Start_Time)-DURATION(Break_Time,"minutes"), "$%Hhours @mminutes")) ## Setting date time fields - Example of setting the date time after 2 hours in the date time item when updating. - DATE_ADD("TODAY",2, "hour", "YYYY-MM-DDTHH:mmZ") - Example to set the date field and the time field together in the date time field. - IF(AND(Date, Time), DATE_FORMAT( DATE_FORMAT(Date,"YYYY-MM-DD") & " " & Time, "YYYY-MM-DDTHH:mmZ")) ## Replace with regular expression - It is a conversion example of dividing numeric figures with a space. With regular expression replacement, add a space before the number, Unnecessary leading spaces are removed in SLICE. - SLICE(REPLACERE(Number,"[0-9]","g"," $&"),1,100) *Number: field code of Number.

Syntax	Example	Reference
# Header	Header	基本排版
- Unordered List	Unordered List
1. Ordered List	Ordered List
- [ ] Todo List	Todo List
> Blockquote	Blockquote
Bold font	Bold font
Italics font	Italics font
~~Strikethrough~~	~~Strikethrough~~
19^th^	19^th
H~2~O	H₂O
++Inserted text++	Inserted text
==Marked text==	Marked text
[link text](https:// "title")	Link
![image alt](https:// "title")	Image
`Code`	`Code`	在筆記中貼入程式碼
```javascript var i = 0; ```	`var i = 0;`
:smile:		Emoji list
{%youtube youtube_id %}	Externals
$L^aT_eX$	L^aT_eX
:::info This is a alert area. :::	This is a alert area.