GiftWrap General Ledger Upload File Layout
GiftWrap can produce three different files that are designed to be uploaded to a general ledger database:
-
Uploading payment information: produced using the Create Payment File for Upload to GL option under Payments > Advices/Files.
-
Uploading FASB information: produced under Review/Analysis > Historical Gift Values.
-
Uploading gift information: produced by clicking
in the Gift Information page for the gift.
In all cases, you can choose between two text formats:
-
Comma and quote delimited (.csv): character values are enclosed in double quotes (e.g., "Joe Donor") and numbers, dollar amounts, and dates are not (e.g., 10/1/2005 or 120412). The fields included in the file and their order are shown below.
-
Fixed length text (.sdf): numbers and dollar amounts are right justified; character values and dates are left justified. Numerical values are left-padded with 0s and text fields are right-padded with spaces. The fields included in each record and their column positions are shown below.
Payments upload file
Field Name in file |
Table.Field |
Position |
Comment |
|
RecordNumber |
[record number] |
1-11 |
Sequential Number (1,2,3) |
|
TenantID |
tblTenant.TenantID |
12-22 |
|
|
OrgID |
tblOrganization.OrgID |
23-33 |
|
|
OrgNickName |
tblOrganization.OrgNickname |
34-43 |
|
|
OrgAccount1 |
tblOrganization.Orgaccount1 |
44-63 |
|
|
OrgAccount2 |
tblOrganization.Orgaccount2 |
64-83 |
|
|
OrgAccount3 |
tblOrganization.Orgaccount3 |
84-103 |
|
|
PaymentDate |
tblPaymentSnapshot.PaymentDate |
104-111 |
mmddyyyy |
|
calPaymentAmountTotal
|
tblPaymentSnapshot.NetPaymentAmount |
112-121 |
includes decimal |
|
calFederalWithheldTotal |
tblPaymentSnapshot.FederalWithheld |
122-133 |
includes decimal |
|
calStateWithheldTotal |
tblPaymentSnapshot.StateWithheld |
134-145 |
includes decimal |
|
calNetAmountTotal |
tblPaymentSnapshot.NetPaymentAmount |
146-157 |
includes decimal |
|
CheckNumber |
tblPaymentSnapshot.CheckNumber |
158-168 |
|
|
Issued Date |
tblPaymentSnapshot.IssuedDate |
169-176 |
mmddyyyy |
|
Cleared |
tblPaymentSnapshot.Cleared |
177 |
N or Y |
|
IssuedType |
tblPaymentIssuedTypeIDLookup |
178 |
C = checking, S = savings |
|
PayTo |
tpersongiftmap.htm.Payto |
179-228 |
|
|
GiftKey |
tblGift.GiftID |
229-239 |
|
|
Gift Type |
tblGiftType.Short |
240-249 |
BEQ |
|
PIFID |
tblPIF.pifID |
250-260 |
|
|
Account1 |
tblGift.Account1 |
261-280 |
|
|
Account2 |
tblGift.Account2 |
281-300 |
|
|
Account3 |
tblGift.Account3 |
301-320 |
|
|
Account4 |
tblGift.Account4 |
321-340 |
|
|
Account5 |
tblGift.Account5 |
341-360 |
|
|
PersonKey |
tblPerson.PersonKey |
361-370 |
|
|
PersonAccount1 |
tblPerson.PersonAccount1 |
371-391 |
|
|
PersonAccount2 |
tblPerson.PersonAccount2 |
392-411 |
|
|
PersonAccount3 |
tblPerson.PersonAccount3 |
412-431 |
|
|
PersonAccount4 |
tblPerson.PersonAccount4 |
432-451 |
|
|
GLCreditAccount |
[user-entered] |
452-471 |
Credit Account for whole batch entered on fly |
|
GLDebitAccount |
[user-entered] |
472-491 |
Debit Account for whole batch entered on fly |
|
GLFileDate |
[user-entered] |
492-499 |
Date entered on fly, applies to whole batch, mmddyyyy |
FASB upload file
When using fixed width format (.sdf), numerics (keys, currency) are left-padded with 0s, dates are formatted mm/dd/yyyy, and text fields are right-padded with spaces. When using .csv format, there is no left or right padding.
Field Name |
Table.Field |
Position |
Comment |
|
Record Number |
[record number] |
1-11 |
Sequential Number (1,2,3...) |
|
Tenant ID |
tblTenant.TenantID |
12-22 |
|
|
OrgID |
tblOrganization.OrgID |
23-33 |
|
|
OrgNickName |
tblOrganization.OrgNickname |
34-43 |
|
|
OrgAccount1 |
tblOrganization.Orgaccount1 |
44-63 |
|
|
OrgAccount2 |
tblOrganization.Orgaccount2 |
64-83 |
|
|
OrgAccount3 |
tblOrganization.Orgaccount3 |
84-103 |
|
|
GiftKey |
tblGift.GiftID |
104-114 |
|
|
Gift Type |
tblGiftType.Short |
115-124 |
BEQ |
|
Account1 |
tblGift.Account1 |
125-144 |
|
|
Account2 |
tblGift.Account2 |
145-164 |
|
|
Account3 |
tblGift.Account3 |
165-184 |
|
|
Account4 |
tblGift.Account4 |
185-204 |
|
|
Account5 |
tblGift.Account5 |
205-224 |
|
|
fmtDate1 |
tblHistoricalValue.PriorValueDate |
225-234 |
FASB valuation date 1, mm/dd/yyyy |
|
fmtValue1 |
tblHistoricalValue.PriorValue |
235-246 |
FASB value on valuation date 1, includes decimal |
|
fmtDate2 |
tblHistoricalValue.HistoricalValueDate |
247-256 |
FASB valuation date 2, mm/dd/yyyy |
|
fmtValue2 |
tblHistoricalValue.HistoricalValue |
257-268 |
FASB value on valuation date 2, includes decimal |
|
fmtDifference |
tblHistoricalValue.HistoricalValue - tblHistoricalValue.PriorValue |
269-280 |
FASB Value 2 - FASB Value 1 |
|
GLCreditAccount |
[user entered] |
281-300 |
Credit Account for whole batch entered on fly |
|
GLDebitAccount |
[user entered] |
301-320 |
Debit Account for whole batch entered on fly |
|
GLFileDate |
[user entered] |
321-340 |
Date entered on fly, applies to whole batch |
Gift upload file
Field Name |
Table.Field |
Position |
Comment |
|
Gift Key |
Gift.Gift_pk |
1-11 |
|
|
Org Key |
Gift.Org_fk |
12-22 |
|
|
Pif Key |
Gift.Pif_fk |
23-33 |
|
|
Gift Type |
Gift.GiftType |
34-37 |
|
|
Gift Subtype |
Gift.Subtype |
38 |
|
|
Account #1 |
Gift.Account1 |
39-56 |
|
|
Account #2 |
Gift.Account2 |
57-74 |
|
|
Gift Date |
Gift.GiftDate |
75-84 |
|
|
Gift Amount |
Gift.GiftAmt |
85-96 |
dddd.cc format |
|
Credit Amount |
Gift.CreditAmt |
97-108 |
dddd.cc format |
|
Payout Percent |
Gift.PayPercent |
109-119 |
|
|
Char Deduction |
Gift.CharDeduct |
120-131 |
dddd.cc format |
|
Endowed |
Gift.Endow |
132 |
0 = no, 1 = yes |
|
Restricted |
Gift.Restricted |
133 |
0 = no, 1 = yes |
|
Temp Restricted |
Gift.TRestricted |
134 |
0 = no, 1 = yes |
|
Reserve State |
Gift.RState |
135-136 |
2-char postal code |