Creating a view across all companies–part 3

This is the final part in a three part series of blog posts. In part 1 I wrote about how you could use a couple of fields in the $ndo$dbproperty table to create a ConvertInvalidChars() function that would turn your NAV company name into the prefix for the table name as it is used in the SQL database. Then in part 2 I showed you how to create a SQL script that would dynamically create your view for all companies that exist in the database. In this final part, I’m going to show you how to link the view to a NAV table and talk about some of the things to watch out for.

If you’ve followed the previous parts, you should now have a SQL view called Company Vendor that contains every field from the Vendor table in NAV plus a new field called Company Name that contains the company name.

Linked Tablesimage

NAV has a neat property on the table definition that allows you to say a table is linked to a view in the database that has the same name as the table name. You can see this property by bringing up the properties for the table and looking at Linked Object. In the next image, you can see the property for my table 50001 is set to Yes. There is another property called LinkedInTransaction and the use of this has often been mysterious, I think because the online help definition is so poor and also because it is very rare for me to try to update the data in the linked object. According to a dynamicsuser.net post from Dean McCrae, when you have LinkedInTransaction set to No, NAV uses a separate connection and separate transaction for accessing the linked tables. This means that if your main connection rollsback due to an error, it does not affect the transaction performed for the linked object. The purpose of LinkedInTransaction is to make the transactions performed on the linked objects isolated from the main transaction. So it only matters if you plan to allow transactions such as inserts, updates and deletes on this linked object. In this example we are not allowing that.

Creating the Linked Table

I have found the quickest way to create my linked table is to edit my original table (in this case the Vendor table) and then edit the properties to change the ID, Name and set the LinkedObject property to Yes and DataPerCompany to no. I can then save my table. Since my view already exists, it all gets plumbed up correctly. I can now run my table and I see data from both of my companies – but unfortunately I can’t see the company name, and that is because I haven’t added the Company Name field. When I edit my table and add the new Company Name field, I come across the first of the problems with this simple technique. I get a message telling me that “The Record variable must belong to 23 and not to 50001”. The reason for this error is that there is programming logic attached to various triggers on the table that is expecting the table to be the Vendor table (23) and not our new Company Vendor table (50001). In short, you are going to need to remove all programming triggers from your table definition. You can do this easily by pressing Ctrl+S to save the table, the error message will be displayed and when you click OK, you will be taken to the code editing window. Now simply press Ctrl+Home to get to the start of the code, hold the Shift key down and press Ctrl+End so that all text in all programming triggers is selected. Then press delete. Finally, call up the Global variables window, open the Functions tab and select all of the functions, then delete. This will remove those functions that get left behind as empty functions when we deleted the code.

Gotchas

Although it was really easy for me to create my linked table by saving the existing table, there are a couple of things wrong with this. First of all, I need to remember to remove the programming code from the table, otherwise bad things could happen unexpectedly (such as data being updated in related tables on validates). The other issue relates to FlowFields. The system does not complain about the flowfield definitions in the table but you should be careful of these. The flow fields (such as Balance) are used to sum up the remaining amount on the Vendor Ledger entries for this Vendor, but the system does not know that the Vendor actually exists in a different company. If you are lucky, the flow fields will simply display 0. If you are unlucky there will be a Vendor with the same code in different companies and as a result, you will see a value that is related to the wrong company. My advice to you is to remove the flowfields and flowfilters from your table definition. On the vendor table this is 56 different fields. It’s at this point that I’m starting to wonder whether I actually saved any time by copying the existing table. It’s not actually that hard to go down the list of fields and delete the ones you don’t think you’ll need. You may find that when you delete certain fields from the table, the system complains that the fields are used in an active key. This is a good reason to delete the keys from the table definition too and make the primary key “Company Name”, “No.”.

Making the Page

My final step is to make a page over my new table. Remember to make the page non-editable and to say Insert Allowed, Delete Allowed and Modify Allowed = No. We don’t want someone accidentally deleting records from the view, because this will delete the actual records from the linked tables (and I have found that it doesn’t care what the underlying table is, even tables like the General Ledger Entry table which you wouldn’t normally be allowed to delete from using a regular user’s license). Here’s a picture of my final page showing how I can search across multiple companies for a specific vendor name.

image 

Have fun with your linked tables.


Related
Recommended