Smart Copy/Paste In Query
Published
There are various changes to query in the more recent versions of The Raiser’s Edge that will make searching that little bit easier. One of these is to copy multiple values into one query using the one of operator.
Let’s say, for instance, you have an Excel spread sheet of 1750 constituents that include their Constituent ID, Address information and Phone numbers. You wish to use Import to update the addresses, however, you know from past experience you need the Constituent Import ID to update the records which you don’t have in your spreadsheet. What can you do to quickly retrieve the Import ID’s of these constituents?
There is a function in The Raiser’s Edge commonly known as Smart copy/paste. This uses the ‘one of’ query operator and allows you to copy the constituent ID’s from Excel into query. The values table only takes a max of 500 entries however you can use the same filter multiple times in the same query until all of your constituent ID’s are input.
First, open a Constituent query then in the criteria select Constituent ID.
Change the operator to ‘one of’ then take the values scroll bar to the very bottom. **This is important**
Next, go into your Excel spreadsheet and copy the first 500 constituent ID’s.
Now come back into query and click your cursor on the bottom row of the values table.
**Note you will not see a flashing cursor but this is to be expected.
Now press Ctrl V on your keyboard to paste the values and scroll back up to see these in the table.
Press 'OK' and repeat the process until all 1750 constituent ID’s have been entered.
Now go to the output tab and output Constituent ID and Import ID (along with any other fields you require).
You can then export the data directly from query and match this to your existing Excel spreadsheet.
Here you can then enter a new column for Import ID and copy and paste the import ID’s to your file for import.
Here is a Knowledgebase solution on How to copy and paste multiple values at the same time into a query using the One Of operator.
This option can be used for almost all one to many fields.
For more information about Query, search Training Central using the keyword Query.
Let’s say, for instance, you have an Excel spread sheet of 1750 constituents that include their Constituent ID, Address information and Phone numbers. You wish to use Import to update the addresses, however, you know from past experience you need the Constituent Import ID to update the records which you don’t have in your spreadsheet. What can you do to quickly retrieve the Import ID’s of these constituents?
There is a function in The Raiser’s Edge commonly known as Smart copy/paste. This uses the ‘one of’ query operator and allows you to copy the constituent ID’s from Excel into query. The values table only takes a max of 500 entries however you can use the same filter multiple times in the same query until all of your constituent ID’s are input.
First, open a Constituent query then in the criteria select Constituent ID.
Change the operator to ‘one of’ then take the values scroll bar to the very bottom. **This is important**
Next, go into your Excel spreadsheet and copy the first 500 constituent ID’s.
Now come back into query and click your cursor on the bottom row of the values table.
**Note you will not see a flashing cursor but this is to be expected.
Now press Ctrl V on your keyboard to paste the values and scroll back up to see these in the table.
Press 'OK' and repeat the process until all 1750 constituent ID’s have been entered.
Now go to the output tab and output Constituent ID and Import ID (along with any other fields you require).
You can then export the data directly from query and match this to your existing Excel spreadsheet.
Here you can then enter a new column for Import ID and copy and paste the import ID’s to your file for import.
Here is a Knowledgebase solution on How to copy and paste multiple values at the same time into a query using the One Of operator.
This option can be used for almost all one to many fields.
For more information about Query, search Training Central using the keyword Query.
News
Raiser's Edge® Blog
08/09/2016 2:35pm EDT
Leave a Comment
Remember, the values table in query will hold a max of 500 rows so if you don't have 500 values copied, you'll need to scroll back up to see them or simply press OK and they'll show in the query criteria filter pane. You can always repeat the process in the same query if you have more than 500 values to paste. I hope this helps!