Why do I have multiple lines for records?
- 04 Jun 2023
- Contributors
- Print
- DarkLight
- PDF
Why do I have multiple lines for records?
- Updated on 04 Jun 2023
- Contributors
- Print
- DarkLight
- PDF
Article summary
Did you find this summary helpful?
Thank you for your feedback
I created a datagrid and I am getting more than one line for my grant. How do I stop this and get only one line?
Why am I missing records and why when I change my table combination do I lose more records?
Answer:
Datagrids display lines based on the selection criteria in the query and the relationship between the various tables joined together to provide you with the columns you requested.
By their very nature unposted gifts and grants are single records. However, fund information is not stored on the grant record but is stored in the payment line tab and for gifts, the values are on the General Ledger tab. The Gift record has the fund information is an array prior to posting.
After posting, fund information remains in the line items table for applications, but also is moved to the funddetailhistory (FDH) table. Gift information will also move to the FDH table.
When you want either gift or grant information along with fund information, the table combination you typically will see is a gifthistory,funddetailhistory combination or an apphistory, funddetailhistory combination.
For just this simple combination, you might get multiple lines if a gift was made up of a gift and non gift or a grant was split between two funds.
If you want the fund reps associated with the fund on the gift or grant, the table combination has to expand.
For gifts this might expand to gifthistory, funddetailhistory, funds,fundreps, profile. You are joining the posted gifts to the FDH record to get the fundid and then looking at the fund association tab to find the people associated with the fund and then looking up the addresses ( perhaps) for those fund reps. If you have 3 fund association records ( one for founder, gets statement, donor advisor), and only one fund on the gift, you will now get a minimum of 3 lines- one for each fund rep type.
If you have a four payment scholarship and 3 fund reps, you now have 12 lines. If you happen to have 2 people getting a statement, so 4 fund rep lines, you now have 16 lines.
So you can see how you can very quickly get multiple lines when tables are joined together.
In the above scenario, if you filter on a fund rep type of founder, you will drop back to 4 lines.
If possible, when creating a datagrid, choose the least number of tables. This will minimize the number of lines. Unfortunately, sometimes multiple lines can't be helped.
If you can, export the grid contents and open in Excel and use the Remove Duplicate feature to retain the important columns of data, but remove what appear to be unnecessary lines.
As you add more tables to the list of tables, the join might cause records to drop because one of the records in the first table doesn't exist in the next.
If you choose the profiles,affpro combination you are selection only those profiles that have affiliation codes. Profiles,donor will get you only the profiles who also have a donor record.
If you want to retain all the profiles in the first table, then the join must be an outer join to not drop records. Check the description of the table combination for the phrase "Outer join" if you don't want to lose records that are not in the subsequent tables.
By their very nature unposted gifts and grants are single records. However, fund information is not stored on the grant record but is stored in the payment line tab and for gifts, the values are on the General Ledger tab. The Gift record has the fund information is an array prior to posting.
After posting, fund information remains in the line items table for applications, but also is moved to the funddetailhistory (FDH) table. Gift information will also move to the FDH table.
When you want either gift or grant information along with fund information, the table combination you typically will see is a gifthistory,funddetailhistory combination or an apphistory, funddetailhistory combination.
For just this simple combination, you might get multiple lines if a gift was made up of a gift and non gift or a grant was split between two funds.
If you want the fund reps associated with the fund on the gift or grant, the table combination has to expand.
For gifts this might expand to gifthistory, funddetailhistory, funds,fundreps, profile. You are joining the posted gifts to the FDH record to get the fundid and then looking at the fund association tab to find the people associated with the fund and then looking up the addresses ( perhaps) for those fund reps. If you have 3 fund association records ( one for founder, gets statement, donor advisor), and only one fund on the gift, you will now get a minimum of 3 lines- one for each fund rep type.
If you have a four payment scholarship and 3 fund reps, you now have 12 lines. If you happen to have 2 people getting a statement, so 4 fund rep lines, you now have 16 lines.
So you can see how you can very quickly get multiple lines when tables are joined together.
In the above scenario, if you filter on a fund rep type of founder, you will drop back to 4 lines.
If possible, when creating a datagrid, choose the least number of tables. This will minimize the number of lines. Unfortunately, sometimes multiple lines can't be helped.
If you can, export the grid contents and open in Excel and use the Remove Duplicate feature to retain the important columns of data, but remove what appear to be unnecessary lines.
As you add more tables to the list of tables, the join might cause records to drop because one of the records in the first table doesn't exist in the next.
If you choose the profiles,affpro combination you are selection only those profiles that have affiliation codes. Profiles,donor will get you only the profiles who also have a donor record.
If you want to retain all the profiles in the first table, then the join must be an outer join to not drop records. Check the description of the table combination for the phrase "Outer join" if you don't want to lose records that are not in the subsequent tables.
Was this article helpful?
