Adding a Multiple Field and Row Conditional Column

[Venkatasai541]

Hi Guys,

I have an input table like this which consists of document numbers with auto-posting (Auto Line) Yes or No:

Document Number	Debit/ Credit	Amount	Auto Line
1900000000	Debit	100	X
1900000000	Credit	100	X
1900000001	Debit	200
1900000001	Credit	200
1900000002	Debit	100	X
1900000002	Debit	100
1900000002	Credit	100	X
1900000002	Credit	100

I want to add a column "Manual Entry?" which shall identify the documents which are not auto-posted on complete document number basis.

Conditions:

1. For the same document number if both debit and credit Iines are auto-posted then in the 'Manual Entry' column - "No" should be updated.

2. If either debit/ credit is auto-posted or both debit, credits are not auto-posted then the column should be updated as "Yes".

Output table should be like this:

Document Number	Debit/ Credit	Amount	Auto Line	Manual Entry?
1900000000	Debit	100	X	No
1900000000	Credit	100	X	No
1900000001	Debit	200		Yes
1900000001	Credit	200		Yes
1900000002	Debit	100	X	Yes
1900000002	Debit	100		Yes
1900000002	Credit	100	X	Yes
1900000002	Credit	100		Yes

Can anyone help me with this problem without using Cross Tab?

[DavidP]

Have a look at the attached to see if that works for you.

[Venkatasai541]

@DavidP - Thanks for your quick response.

The input lines are not standard. Sometimes for a single document number, there can be 20 line items also.

Your workflow is working for the given input but if I change the input as follows it is not working:

Try #1:

Document Number	Debit/ Credit	Amount	Auto Line
1900000000	Debit	100	X
1900000000	Credit	100	X
1900000001	Debit	200
1900000001	Credit	200
1900000002	Debit	100	X
1900000002	Debit	100
1900000002	Credit	100	X
1900000002	Credit	100
1900000002	Debit	100	X
1900000002	Credit	100

Final Output #1:

Document Number	Debit/ Credit	Amount	Auto Line	Manual Entry
1900000000	Debit	100	X	No
1900000000	Credit	100	X	No
1900000001	Debit	200		Yes
1900000001	Credit	200		Yes
1900000002	Debit	100	X	Yes
1900000002	Debit	100		Yes
1900000002	Credit	100	X	Yes
1900000002	Credit	100		Yes
1900000002	Debit	100	X	Yes
1900000002	Credit	100		Yes

Try #2:

Document Number	Debit/ Credit	Amount	Auto Line
1900000000	Debit	100
1900000000	Credit	100
1900000001	Debit	200
1900000001	Credit	200
1900000002	Debit	100
1900000002	Debit	100
1900000002	Credit	100
1900000002	Credit	100
1900000002	Debit	100
1900000002	Credit	100

Final Output #2:

Document Number	Debit/ Credit	Amount	Auto Line	Manual Entry
1900000000	Debit	100		Yes
1900000000	Credit	100		Yes
1900000001	Debit	200		Yes
1900000001	Credit	200		Yes
1900000002	Debit	100		Yes
1900000002	Debit	100		Yes
1900000002	Credit	100		Yes
1900000002	Credit	100		Yes
1900000002	Debit	100		Yes
1900000002	Credit	100		Yes

I have created the workflow using crosstab by creating columns at "Auto Line". The only issue is if there is no "x" in the column then the workflow is failing like in Try #2. (Attached the workflow)

-

Venkat

[Venkatasai541 · Accepted solution]

@DavidP  I got the solution. I just made some changes to your workflow and now it is working. Thanks.

Attached the workflow.

-

Venkat