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?
Solved! Go to Solution.
@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
@DavidP I got the solution. I just made some changes to your workflow and now it is working. Thanks.
Attached the workflow.
-
Venkat