Linking with a Formula Field
Linking records in a subreport can also be done using a formula field. This gives you more creativity for how it is linked to the main report. There are many reasons for wanting to link with a formula field rather than a data field.
When using a formula to link to a subreport, you aren’t restricted to using indexed fields. Linking with formulas lets you use any field in the table to link the two reports together. As long as the data in both tables match, you’re good to go.
It is a common task to have to link tables from two different programs together when the data isn’t compatible. Using formulas to parse or join multiple data fields and using the results to link to a subreport is perfect for this. This situation can happen when the programs were developed by different teams in the same company or when one company acquires another company and they have to consolidate their data. Formulas give you the flexibility to massage the data from one table into a format compatible with the data in another table. This can consist of converting the field to a different data type, concatenating multiple fields together or parsing a field to extract out the extraneous characters.
Telling Crystal Reports to link on a formula involves the same steps as linking on a data field, the key difference is that you have to make sure that the formula is already created prior to creating the subreport. When you create the subreport and click on the linking tab, the formula is shown in the Available Fields list on the left. Simply double-click on it to add it to the Fields to Link To list on the right.
Unlike other dialog boxes in Crystal Reports, the Link tab doesn’t have a Formula button to create a new formula at that moment. That is why if you want to link your subreport using a formula, then you have to create it before the subreport. Otherwise, you have to close this dialog box, go back to the main report to create the formula, and then edit the subreport again to link on the formula. A little pre-planning makes your life easier!
Once the formula field has been added, the frame at the bottom of the dialog box appears and it works the same as adding a data field: the parameter name is automatically filled in and you pick a subreport field from the dropdown box to link to.
Here are some examples of useful formulas for linking subreports.
Linking on multiple fields
Formulas are often used for linking concatenated string fields together. It is common to have tables with similar information, but a slightly different structure.
For example, the part number for an inventory item is usually broken up into different groups of numbers and each group has a different meaning. The groups could be category, product grade, and item number. One table could list the inventory number as a single field with each group separated by dashes (e.g. 999-99-9999) and another table could list the number across three different fields (one field for each group). Normally, it isn’t possible to link these two tables together because you can’t link one field to three different fields. However, you can use a formula to combine the three fields so that they match the format of the field in the other table. For example, we can create a formula that combines the three fields together and link the formula to the other table.
Linking on a non-indexed field
As we just mentioned, formulas can be used to link on non-indexed fields. This is a very simple process. Simply create a new formula and set it equal to the non-indexed field. Then use that formula as the linking field. In the example below, the field Customer Credit ID is not indexed, but you can link to it using a formula field.
This formula simply repeats the name of the field in it. It’s very simple, but that is all that is needed when the field isn’t indexed.
Linking on a partial string
There are times when a field has too much information in it. You only need part of the string for linking to another table. Consider the previous inventory example where the table has all three groups of the inventory number in one field. The middle portion of the string is the product grade and you want to link to a subreport which summarizes the sales for all products with that specific grade. You can’t link to the whole inventory number because it has the other grouping data in it as well. Instead, you have to pull out just the product grade portion and use that to link to the subreport.
In this formula, the Inventory ID field stores the product grade as the middle portion of the Inventory ID. So the formula uses the Mid() function to extract the two middle characters from the string, starting at position five. Now the formula can be used to link to a subreport which summarizes data on product grade.
A very common report type is a directory listing. This report groups everything by the first letter of the person’s name (or product name). So the first group is on the letter A and the second group is on the letter B, etc. When you put a subreport in the group header or footer, you want the subreport to also print by the same letter. The formula for doing this is easy enough. Use the Left() function to parse out the first letter of the product name.
What is good about this example is that the formula for grouping the data in the main report is also the same formula for linking to the subreport. The main report groups all its data on the first letter of the product name and so does the subreport. You don’t have to create an extra formula for linking to the subreport. Use the same one that you used for creating the groups.
Linking with Memo Fields
Crystal Reports doesn’t let you use a memo field to link reports together. Memo fields aren’t displayed in the Subreport Links dialog box. However, you can use a formula field instead. Create a formula and make it equal to the memo field. Now when you go to link to the subreport you’ll see the new formula in the Available Fields list.
Converting data types
A common problem with databases is that they can be developed over a long period of time and worked on by different people. This creates a situation where the same fields can have different data types in different tables. Ideally, there would be clear specifications on the data type that each field has. But unfortunately this doesn’t happen often enough.
For example, a company could originally have their inventory IDs being a combination of strings and numbers. But at a later point in time, they decide to buy new inventory management software and it only uses numbers. It’s easy enough to convert the original inventory IDs to numbers, but the problem comes when you try to build a report with the new tables and include a subreport that uses historical data from the archived tables. You can’t link the tables together because the original table uses a string data type and the new inventory package uses a numeric data type. To fix this problem, you have to convert the field from the new inventory system into a string so that it can link to the subreport that uses the original data.
This formula uses the CStr() function to convert the number to a string. It also specifies that we want zero decimal places so that it returns a whole number.
