If formula

[ashaik1]

Hello - Taking the Impact column as a base in consideration with the Account/Department/Intercompany i need to derive the Journal type column.

For example: Journal entry 1, 4, 5 are B/S Only  but having different scenarios.

• Journal entry 1 is B/S only with different accounts and different departments - Then Journal type is B/S reclass or GND reclass
• Journal entry 4 is B/S only with same accounts, no intercompany but different departments - Then Journal type is DEPT transfer
• Journal entry 5 is B/S only with same accounts, same departments but one line is having intercompany and other line does not - Then journal type is Intercompany transfer

Except the Journal 5 scenario the intercompany code may exist or may not exist in the other journal entries

This is how i need to define the Journal type. Please help

Journal Entry	Account	Department	Intercompany	Nature	Impact	Journal type
1	100000	12345	DEF	Asset	B/S Only	B/S reclass or GND reclass
1	200000	34567		Liability	B/S Only	B/S reclass or GND reclass
2	400000	89111		Revenue	IS Only	I/S reclass or GND reclass
2	500000	12134		Expense	IS Only	I/S reclass or GND reclass
2	700000	15167		Expense	IS Only	I/S reclass or GND reclass
3	100000	18192	GHI	Asset	B/s and IS	MAN PL ADJ
3	400000	21223		Revenue	B/s and IS	MAN PL ADJ
4	100000	99999		Asset	B/S Only	DEPT Transfer
4	100000	88888		Asset	B/S Only	DEPT Transfer
5	100000	12345	ABC	Asset	B/S Only	Intercompany transfer
5	100000	12345		Liability	B/S Only	Intercompany transfer

[Yoshiro_Fujimori]

Hi @ashaik1 ,

I tried to follow your expected outcome. I hope this may help.

Worklfow

Formula tool 1

sameAccount = [CountDistinct_Account]=1

sameDepartment = [CountDistinct_Department]=1

Formula tool 2

Journal type = 

IF ![sameAccount] AND ![sameDepartment] AND [Impact] = "B/S Only"
THEN "B/S reclass or GND reclass"
ELSEIF ![sameAccount] AND ![sameDepartment] AND [Impact] = "IS Only"
THEN "I/S reclass or GND reclass"
ELSEIF [Impact] = "B/s and IS"
THEN "MAN PL ADJ"
ELSEIF [sameAccount] AND ![sameDepartment] AND [CountNonNull_Intercompany] = 0
THEN "DEPT Transfer"
ELSEIF [sameAccount] AND [sameDepartment] AND [CountNonNull_Intercompany] = 1
THEN "Intercompany transfer"
ELSE Null()
ENDIF

Output