Using Query Manager to Join Records in BFS
It is possible to join multiple records together using BFS Query Manager.
This job aid will explain how to join together two individual records.
For instructions and examples on how to create and save a query (including adding records, fields, and criteria), please see the previous job aid:
1) Add the First Record
For this example, let's first search for and add the following record: JRNL_HEADER
Choose Fields:
- Business Unit
- Journal ID
- Journal Date
- Source
Add Criteria:
- Limit the Business Unit to 10000
- Include Journal Date criteria for the month of January
Once you have your first record added (including fields and criteria), you can add a second record to join to your first record.
2) Join the Second Record
- From the Records page, search for the second record you want to join with the first record
Let's use the example: JRNL_LN
- Type the name of the record you wish to search for in the search field (which is labeled 'begins with')
- You can click the 'Search' button, or press Enter on your keyboard to display the search results
- Click 'Join Record'
Join Type
- In the Join Type dialogue box, leave the Join Type set to the default, which is Standard Join
Here we are joining the Journal Line (JRNL_LN) record with the Journal Header (JRNL_HEADER) record.
- Click the 'A = JRNL_HEADER - Journal Header Data' link to open the Auto Join Criteria dialogue box
Auto Join Criteria
Since every journal within a Business Unit has a unique Journal ID, we will use that as the matching criteria.
In other words, we will only tie in information from our Journal Line record if it shares the same Business Unit and Journal ID number.
For this example, let's select only the Business Unit and Journal ID criteria.
- Finalize the join by clicking 'Add Criteria'
From the Query page, we can see that both records are now joined under the Chosen Records section, and have been labeled as Record A and Record B.
- You can click the 'Show/Hide' buttons on the left (designated with folder icons) to show and hide the Fields within each record
3) Add Criteria
It is also possible to add and join criteria from the second record with the first record.
For this example, let's say we only want rows where an account is equal to a certain value, or rows from a specific account.
- From the Query page, check the 'Fields' box next to the Account field
- Click the 'Criteria' button for the Account field
- Choose 'Equal To' in the Condition Type drop down menu
- Type the Account number you wish to isolate in the Define Constant input field
- Click the 'OK' button
For this example, let's use Account number: 51210
4) Save and Run
- You can save your query by clicking the 'Save' button
- Click 'Run' to view the output of the query
Our example query now includes everything from the month of January for account number 51210 under 10000.
- Click 'Download to Excel' to save your query as an Excel spreadsheet
