TAPSS - EOFY Issues (Receipting 06-Jul to 07-Jul-2023 Run)

Issues/Errors Encountered and Workaround

Background

During the initial attempt of the Receipting process, some related flows and process builders were producing errors, halting the completion of the process. These flows and process builders are listed below:

Process Builder - Automate task for GA for Pledge (v2)
Process Builder - Description - HOLD RECEIPT (v4)
Flow - AQC Donor Contact Type Auto-Updater (v2)
Flow - (Affinaquest) Update Gift Designations (v1)

Screenshots of the errors that were encountered

The Workaround

Since the Receipting process is important, and there’s no quick fix that can address the errors being encountered when the above process builders and flows are enabled, it was decided that these automations will just be disabled temporarily, and then manually do the intended updates/operation of each, once the Receipting process is completed.

Process Builder - Automate task for GA for Pledge - Disabled

The process builder creates Task record for each updated Gift/Pledge record that went into ‘Pending review’ status. Since this is disabled during the Receipting process, due checks were done to determine if there are Gift/Pledge records that require manual creation of the relevant Task record.

Screenshot

Below are the steps done to check if there are records that require manual creation of Task record:

First check: SOQL to see if there are Gifts/Pledges records that are in ‘Pending review’ covering the FY.

SELECT Id, AQB__Gift__r.name,AQB__Gift__r.USyd_Pledge_Status__c
FROM AQB__Transaction__c
WHERE DAY_ONLY(CreatedDate) >= 2022-07-01
  AND DAY_ONLY(CreatedDate) <= 2023-06-30
  AND AQB__Gift__r.USyd_Pledge_Status__c = 'Pending review'

Result: The above SOQL, when summarized resulted in 5 Gift/Pledge Records, which already has automatically created Task records. No manual record update or record creation done.

Gift/Pledge Number	Comments
2341283	With automatically generated task
2345964	With automatically generated task
2359231	With automatically generated task
2368046	With automatically generated task
2370440	With automatically generated task

Second check: SOQL to check if there are Gift/Pledge that are in ‘Pending review’ based Transaction records' related Receipt’s creation date (vs Receipting date).

SELECT COUNT()
FROM AQB__Transaction__c
WHERE DAY_ONLY(AQB__Receipt__r.CreatedDate) = 2023-07-07
  AND AQB__Gift__r.USyd_Pledge_Status__c='Pending review'

Just another approach of checking, but the goal is the same - to identify if there are Gift/Pledge records that are in ‘Pending review’ that may need manual creation of relevant Task record.

Result: The above SOQL yielded 0 result. This means no Gift/Pledge record requires manual creation of auto-generated Task record. No manual record update or record creation done.

Process Builder - Description - HOLD RECEIPT - Disabled

Screenshot

This process builder is triggered when Transaction is created or edited. This process builder has two criteria nodes, and so the details below were segregated per criteria node. Here are the criteria nodes based on each criteria node name:

Check if the receipt on hold is checked.
Copy description to text area field

Node 1: “Check if the receipt on hold is checked.”

Condition (translated to semi pseudo-code):

AQB__OriginatingBatchItemTransaction__r.AQB__BatchItemID__r.AQB__ApproveReceipt__c IS TRUE
AND AQB__OriginatingBatchItemTransaction__r.AQB__Description__c IS NOT NULL
AND AQB__Batch__r.RecordTypeId='0124a0000004KatAAE'

Action:

Action Operation: Update Records
Name: Update Receipt Description
Target Record(s): Related Gift/Pledge Record’s Receipts
Target Field(s): Receipt On Hold Reason

Since this process builder was disabled, the following SOQL statement was executed to check if there are records that need manual update. This SOQL statement is based on the criteria defined in this criteria node.

SELECT COUNT() FROM AQB__Transaction__c
WHERE DAY_ONLY(AQB__Receipt__r.CreatedDate) = 2023-07-07
  AND AQB__Batch__r.RecordTypeId='0124a0000004KatAAE'
  AND AQB__OriginatingBatchItemTransaction__r.AQB__BatchItemID__r.AQB__ApproveReceipt__c=TRUE

Result: The SOQL above produced 0 rows. This means that if the process builder was enabled during the Receipting process, no record will be processed by this particular node because no records met the defined condition/criteria, hence no record are to be manually updated. No manual record update or record creation done.

Node 2: Copy description to text area field

Condition (translated to semi pseudo-code):

Transaction Description field IS NOT NULL
AND Transaction Description field ISCHANGED

Action

Type: Update Records
Name: copy details to text area field
Target Record(s): (Current) Transaction
Target Field(s): Text Area Description

Since this process builder was disabled, based on the above condition of this criteria node, the following SOQL statement was executed to check if there are records that need manual update.

SELECT COUNT() FROM AQB__Transaction__History
WHERE Field='AQB__Description__c'
AND ParentId IN (
  SELECT Id
  FROM AQB__Transaction__c
  WHERE DAY_ONLY(AQB__Receipt__r.CreatedDate) = 2023-07-07
)

AQB__Transaction__History object is used to determine if there were changes that happened on the applicable Transaction records' AQB__Description__c field, which is part of the condition (Is changed operator).

Result: This SOQL statement returned 0 when executed, hence there’s no record to update or process manually. No manual record update or record creation done.

Restored Receipts: From Voided to Unvoided

Account: ID	Account	Amount	Gift/Pledge Number	Last Modified By	Last Modified Date	Receipt Number	Voided Date	Voided Date	Gift Type	Acknowledgement	Receipt Id
0014a000007pT3RAAU	Joye Foundation Pty Ltd	1000	2367234	Monica Kluegel	06/07/2023 15:07	13312	06/07/2023 0:00	45113	Gift	Email - Receipt	a2J8v0000004bhNEAQ
0014a000007pT3RAAU	Joye Foundation Pty Ltd	4000	2367235	Monica Kluegel	06/07/2023 15:07	13314	06/07/2023 0:00	45113	Gift	Email - Receipt	a2J8v0000004bhPEAQ
0014a000007oDiMAAU	Valherb Pty Ltd ATF The Herbert & Valmae Freilich Prescribed Private Fund	25000	2367355	Monica Kluegel	06/07/2023 15:07	13385	06/07/2023 0:00	45113	Gift	Email - Receipt	a2J8v0000004iU9EAI
0014a000007pQ92AAE	Australian Philanthropic Services Foundation	25000	2367309	Monica Kluegel	06/07/2023 15:07	13424	06/07/2023 0:00	45113	Gift	Email - Receipt	a2J8v0000004iUvEAI
0014a000007pCBIAA2	The Mill House Foundation	100000	2367720	Monica Kluegel	06/07/2023 15:07	13585	06/07/2023 0:00	45113	Gift	Email - Receipt	a2J8v0000004shBEAQ
0014a000007nDJOAA2	Ms Alice H Y Wong	560	2368102	Yeng Sembrano	10/07/2023 4:41	13726	06/07/2023 0:00	45113	Gift	Email - Receipt	a2J8v0000004yDYEAY
0018v000001njRUAAY	K A McClellan Limited ATF the Kathleen McClellan Private Ancillary Fund	10000	2358463	Monica Kluegel	07/07/2023 13:36	2570	07/07/2023 0:00	45114	Gift	Default	a2J8v000000009lEAA
0014a000007pjSFAAY	Nick van Gelder Dreamers Foundation	50000	2361559	Monica Kluegel	07/07/2023 13:48	9760	07/07/2023 0:00	45114	Pledge	Default	a2J8v0000000AVvEAM
0014a000007mhnYAAQ	Dr Joan M Barber	5700	2366447	Monica Kluegel	07/07/2023 13:13	12839	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v00000046DEEAY
0014a000007my82AAA	Dr Steven K C Lee	5700	2366448	Monica Kluegel	07/07/2023 13:56	12841	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v00000046DGEAY
0018v000001njRUAAY	K A McClellan Limited ATF the Kathleen McClellan Private Ancillary Fund	15000	2366525	Monica Kluegel	07/07/2023 13:36	12872	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v000000492VEAQ
0014a000007oNP3AAM	Fogo Pty Ltd	100000	2367033	Monica Kluegel	07/07/2023 10:59	13189	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v0000004ST6EAM
0018v0000042DvXAAU	EROLBRIDGE PTY. LIMITED & The Trustee for Reyes Family Trust & The Trustee for The Bourke Family Trust trading as Penrith Physiotherapy Sports Centre	1600	2367246	Monica Kluegel	07/07/2023 10:58	13317	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v0000004bhSEAQ
0014a000007pg2FAAQ	Bierzonski Burczyk Foundation	35000	2367360	Monica Kluegel	07/07/2023 10:58	13455	07/07/2023 0:00	45114	Pledge	Email - Receipt	a2J8v0000004jszEAA
0018v000003kclpAAA	Royal North Shore Hospital Medical Association	15000	2367398	Monica Kluegel	07/07/2023 10:58	13463	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v0000004jt7EAA
0014a000007n2OcAAI	Dr Alan G Swan	50000	2367597	Monica Kluegel	07/07/2023 10:58	13545	07/07/2023 0:00	45114	Gift	Default	a2J8v0000004o5tEAA
0014a000007plBiAAI	The Trustee for A. Horadam Burton Foundation	2000	2367595	Monica Kluegel	07/07/2023 11:10	13551	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v0000004o5zEAA
0014a000007oZ8JAAU	Vonwiller Foundation	568000	2367352	Monica Kluegel	07/07/2023 10:58	13553	07/07/2023 0:00	45114	Pledge	Email - Receipt	a2J8v0000004o61EAA
0014a000007mTX6AAM	Dr Keith S K Ong and Dr Eileen Ong	233000	2367714	Monica Kluegel	07/07/2023 10:59	13602	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v0000004shWEAQ
0018v0000037gtHAAQ	Stellar Development Foundation	42900.63	2367715	Monica Kluegel	07/07/2023 10:58	13608	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v0000004shcEAA
0014a000007pGYfAAM	Mr Peter H Butts	60000	2367769	Monica Kluegel	07/07/2023 10:58	13649	07/07/2023 0:00	45114	Pledge	Email - Receipt	a2J8v0000004uASEAY
0014a000007mnGVAAY	Mrs Karen A Moses	5000	2368112	Monica Kluegel	07/07/2023 10:59	13735	07/07/2023 0:00	45114	Gift	Default	a2J8v0000004yDhEAI
0014a000007p97XAAQ	Ainsworth 4 Foundation Pty Ltd ATF The Ainsworth 4 Foundation	75000	2367853	Monica Kluegel	07/07/2023 10:59	13772	07/07/2023 0:00	45114	Pledge	Email - Receipt	a2J8v0000004yEIEAY
0014a000007o1voAAA	Hill Foundation Pty Ltd	30000	2367855	Monica Kluegel	07/07/2023 10:58	13782	07/07/2023 0:00	45114	Pledge	Email - Receipt	a2J8v0000004yESEAY
0018v000004AnIhAAK	Ms Jane Bowden	100000	2368046	Monica Kluegel	07/07/2023 10:59	13790	07/07/2023 0:00	45114	Pledge	Email - Receipt	a2J8v0000004yEaEAI
0014a000007nnQMAAY	Mr Emil N Dan, AM	30000	2368096	Monica Kluegel	07/07/2023 10:59	13794	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v0000004yEeEAI
0014a000007njjSAAQ	Macquarie Bank Limited	961	2368278	Monica Kluegel	07/07/2023 13:48	13814	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v0000004yFjEAI
0014a000007mp8eAAA	Miss Elizabeth S Bowman	20000	2368377	Monica Kluegel	07/07/2023 10:59	17285	07/07/2023 0:00	45114	Pledge	Email - Receipt	a2J8v0000005HgAEAU
0018v000003ogM0AAI	Ms Leith Brooke	5000	2368411	Monica Kluegel	07/07/2023 11:27	17291	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v0000005J16EAE
0018v000003PZ2IAAW	Dr Xuanan Phan	10000	2367137	Monica Kluegel	07/07/2023 13:05	17303	07/07/2023 0:00	45114	Pledge	Email - Receipt	a2J8v0000005QdtEAE
0014a000007nD3hAAE	Mr Brian D Abel	14000	2368708	Monica Kluegel	07/07/2023 11:27	17377	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v0000006S15EAE
0018v000004hKNfAAM	The Gregg Foundation	50000	2369035	Monica Kluegel	07/07/2023 13:05	17794	07/07/2023 0:00	45114	Pledge	Email - Receipt	a2J8v0000007FdNEAU
0014a000007pjlbAAA	Mr Peter D Robertson	10000	2369068	Monica Kluegel	07/07/2023 13:12	17822	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v0000007LHBEA2
0014a000007pA5LAAU	Trustees for the Joland Brummer Trust Settlement	110000	2369946	Monica Kluegel	07/07/2023 13:48	18537	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v000000CQKCEA4
0014a000007pZqDAAU	TDM Charitable Foundation	6000	2370246	Monica Kluegel	07/07/2023 13:21	19098	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v000000CaVgEAK
0014a000007mwVpAAI	Mrs Audrey M Bersten	5000	2370354	Monica Kluegel	07/07/2023 13:36	19180	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v000000Ct4MEAS
0014a000007opDwAAI	First Sentier Investors (Australia) Services Pty Ltd	32900	2370503	Monica Kluegel	07/07/2023 13:36	19230	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v000000DBaXEAW
0014a000007mYZmAAM	Mr Angus N Holden and Ms Kimberley Holden	50000	2370557	Monica Kluegel	07/07/2023 13:48	19269	07/07/2023 0:00	45114	Pledge	Email - Receipt	a2J8v000000DD5vEAG
0014a000007nkx9AAA	Trustees of the Claffy Foundation	21000	2370529	Monica Kluegel	07/07/2023 13:36	19395	07/07/2023 0:00	45114	Pledge	Default	a2J8v000000FInzEAG
0014a000007obOaAAI	Ms Gabrielle A Ewington	20000	2369672	Monica Kluegel	07/07/2023 13:48	19407	07/07/2023 0:00	45114	Pledge	Email - Receipt	a2J8v000000FIoBEAW
0014a000007pA5LAAU	Trustees for the Joland Brummer Trust Settlement	20000	2370850	Monica Kluegel	07/07/2023 13:36	19419	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v000000FMHFEA4
0018v000001njRUAAY	K A McClellan Limited ATF the Kathleen McClellan Private Ancillary Fund	15000	2370955	Monica Kluegel	07/07/2023 13:36	19499	07/07/2023 0:00	45114	Gift	Default	a2J8v000000FXomEAG
0014a000007nkx9AAA	Trustees of the Claffy Foundation	21000	2370529	Monica Kluegel	07/07/2023 14:28	19545	07/07/2023 0:00	45114	Pledge	Default	a2J8v000000G1UFEA0
0014a000007no8jAAA	Lady Potter, AC CMRI	1000000	2371456	Monica Kluegel	07/07/2023 13:56	19680	07/07/2023 0:00	45114	Gift	Default	a2J8v000000Id8qEAC
0014a000007nkx9AAA	Trustees of the Claffy Foundation	21000	2370529	Monica Kluegel	07/07/2023 14:28	19748	07/07/2023 0:00	45114	Pledge	Default	a2J8v000000JObQEAW
0014a000007mt6IAAQ	Mr Alexander Vance Walter Borland	30000	2371617	Monica Kluegel	07/07/2023 13:56	19776	07/07/2023 0:00	45114	Gift	Default	a2J8v000000JUTgEAO
0014a000007nlndAAA	Fund for Jewish Higher Education	22295	2371646	Monica Kluegel	07/07/2023 13:56	19786	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v000000JanDEAS
0014a000007pirwAAA	The Trustee for Oranges & Sardines Foundation	25000	2371726	Monica Kluegel	07/07/2023 14:28	19809	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v000000MLQTEA4
0014a000007nkx9AAA	Trustees of the Claffy Foundation	21000	2370529	Monica Kluegel	07/07/2023 14:28	19810	07/07/2023 0:00	45114	Pledge	Default	a2J8v000000MLQdEAO
0018v0000064YBbAAM	Miss Isabella R Dolan	11500	2371807	Monica Kluegel	07/07/2023 14:44	19869	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v000000MolfEAC
0018v000005HZOCAA4	Oracle Corporation	110000	2371890	Monica Kluegel	07/07/2023 14:52	19899	07/07/2023 0:00	45114	Pledge	Non Research Grant	a2J8v000000N38LEAS
0018v000007OZ80AAG	Ms Beverley Malone	200	2372480	Monica Kluegel	07/07/2023 14:52	20322	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v000000cXOrEAM
0014a000007n9F1AAI	Mr Paul S Martin	1000	2360829	Yeng Sembrano	10/07/2023 4:40	4023	07/07/2023 0:00	45114	Gift	Default	a2J8v00000000ZVEAY
0014a000007oGkqAAE	Australian Executor Trustees Limited	6269.71	2363163	Yeng Sembrano	10/07/2023 6:13	10267	07/07/2023 0:00	45114	Gift	Default	a2J8v0000000VPaEAM
0018v000003ogM0AAI	Ms Leith Brooke	2500	2368113	Yeng Sembrano	10/07/2023 6:40	13741	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v0000004yDnEAI
0014a000007oxpJAAQ	Mr Sanjeev Goyal	20000	2368042	Yeng Sembrano	10/07/2023 6:11	13766	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v0000004yECEAY
0014a000007mg5ZAAQ	Mr John G H Stumbles and Mr Nicholas R Korner	500	2370508	Yeng Sembrano	10/07/2023 7:33	19222	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v000000DBa8EAG
0018v000006AaScAAK	Ms Anastasia Drexler	250	2370509	Yeng Sembrano	10/07/2023 6:44	19224	07/07/2023 0:00	45114	Gift	Default	a2J8v000000DBaAEAW
0014a000007mUVEAA2	Mr Roderick C Best and Dr Louise A Baur	5000	2372096	Yeng Sembrano	10/07/2023 7:10	20042	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v000000OeWvEAK
0014a000007p0iiAAA	Susan & Isaac Wakil Foundation	853302	2362272	Yeng Sembrano	10/07/2023 6:57	20460	07/07/2023 0:00	45114	Pledge	Default	a2J8v000000knu9EAA
0014a000007mw9zAAA	Mr John G Hocking	5000	2373220	Yeng Sembrano	10/07/2023 6:36	20779	07/07/2023 0:00	45114	Gift	Email - Receipt	a2J8v000000sTxOEAU
0014a000007plBeAAI	Mrs Nathalie L Quinlivan	12000	2373364	Yeng Sembrano	10/07/2023 4:27	20842	07/07/2023 0:00	45114	Gift	Default	a2J8v000000sW0CEAU