Why is my query giving me strange results

Sylvia Cook
Sylvia Cook
Sixth Anniversary Kudos 5 Name Dropper Participant
in Blackbaud Financial Edge NXT® General Discussions

I have been looking at queries on and off for a while now, and keep running into this same problem. I want a query of GL transactions that includes the project ID field. When exporting, the sum of the amounts should be zero, but it isn't.

This is what I am doing. Query starting with "Account", the criteria is date for this fiscal year, and select on fund. Output is all columns similar to when I look in NXT account screen, but excluding project columns. When I export to Excel, it will balance to zero so long as I exclude unposted transactions.

BUT, when I add project IDs to the output for the query and export, it doesn't balance to zero. Reviewing the rows, I can see duplicate rows. Even if I remove the duplicate rows, it still does not balance to zero. I think it has to do with how it is picking up transactions that have a split on them. I filtered down to one split transaction in the export, and compared it to how it looks in the GL account in NXT, and the amounts are different. It seems to be picking up the amount before the split on the export.

Has anyone else experienced this? How can I get a full detail GL listing with project ids?

Answers

  • Kevin Brazell
    Kevin Brazell Blackbaud Employee
    Tenth Anniversary Kudos 5 First Reply Name Dropper
    https://community.blackbaud.com/discussion/85095/why-is-my-query-giving-me-strange-results

    Do you mean you're using an account query, or transaction?

  • Sylvia Cook
    Sylvia Cook
    Sixth Anniversary Kudos 5 Name Dropper Participant

    account query. I tried the transaction query, but it had more problems.

  • Kevin Brazell
    Kevin Brazell Blackbaud Employee
    Tenth Anniversary Kudos 5 First Reply Name Dropper

    Ok if you're looking for lines from JE batches, it would be transaction query. You can put Project ID and it should show each line's project ID field. What kind of problems were you having with the transaction query?

  • Sylvia Cook
    Sylvia Cook
    Sixth Anniversary Kudos 5 Name Dropper Participant

    I am getting duplicate lines when I add the project ID. But I believe it is only for transactions that were entered with splits. Basically, the query is duplicating the row for each split in the transactions, and it is putting in the transaction amount, not the split amount.

    In reading more into things, it seems that I need to run an export, not a query. The export got me further to what I am looking for, but I still run into a problem with split entries. At least see them. I was hoping to get an export in a clean format so I can use it as a flat file for Power BI, but the split transactions are messing it up. :(

    My ultimate end goal is to get Power BI connected via Sky/API…. but that connection piece seems very intimidating at the moment, so I thought it would be easier to get a flat file first. I am getting more familiar with how the data looks, so a good learning experience. Just not as quick and easy as I thought.

  • Kevin Brazell
    Kevin Brazell Blackbaud Employee
    Tenth Anniversary Kudos 5 First Reply Name Dropper
    https://community.blackbaud.com/discussion/comment/312144#Comment_312144

    OK yes, the splits are what KB calls a One to many relationship. It's not a true duplicate as the different data, the splits, shows the lines over , but the difference being the project id and or amount. Here is a KB to determine if a field is a one to many relationship : https://kb.blackbaud.com/knowledgebase/articles/Article/205268

  • Sylvia Cook
    Sylvia Cook
    Sixth Anniversary Kudos 5 Name Dropper Participant

    @Kevin Brazell Would you happen to know a way to get an export with the splits in the amount column so the data is a more usable table? My goal is to use it as a flat file for Power BI, but with the splits running off the side of a row, the data is not usable. If I look at an account in the screen, any split amounts are in the column.

Categories