Archives

The T-SQL Implications with FastForms Customizations

In this article I’ll focus on the T-SQL component of a successful FastForms customization involving extension tables. I tend to demonstrate things in very small steps, and often work that way as well. This would be a good time to repeat my standard disclaimer:

Important: All information on this site is provided “as is” for informational purposes only.
In particular, all code samples are provided “as is” without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

If you should decide to use any of the code from this site, make sure you test it thoroughly on a test company (or better yet a test server) before you ever use it on your live data.

Acme Product Testing

FastForm Field Creation Basics

In our example, we work for Acme Inc. which sells rockets. As the Admin, Acme has tasked you to create a method to track which color rockets are ordered. In the past Acme accomplished this with separate part numbers for each color, and each of their rocket part numbers begin with a color code. Acme wants to separate the color component from their part numbers.

I’m not going to discuss the basics as to how to create FastForms screen customizations. Made2Manage offers a downloadable class for that. For the sake of brevity, lets assume that the Admin has already created a combo box for color on the Sales Order Release Screen(SORELS) and populated it with the appropriate values. To create the FastForms extension table, click on the Data Tab of the FastForms Tool Box and expand SORELS as seen on Figure 1 below. Right click Extension Fields and select Add Field.

Add Table

Figure 1


Name your new field in the Field Properties and select a Character type of length 10 (figure 2 below). I chose 10 because most color names require less than 10 letters.

Figure 2


M2M will create the table and field in the SQL database and give you the warning below. This is helpful, but it would be more helpful if the warning didn’t trigger every single time you added a field. After you create the field, assign it to the control under the Properties Tab.

Figure 3

How do I link my extension table into reports?

The process is fairly simple once you understand how the extension tables work. In this case, a SORELS_EXT table was created and that table links to the SORELS table via the FKey_ID field. M2M also creates a stored procedure which handles extension table activities such as inserts and updates. The link for reporting would look like this:

SELECT * FROM SORELS SOR
     LEFT OUTER JOIN SORELS_EXT SORe
     ON SOR.IDENTITY_COLUMN = SORe.FKEY_ID

Incidentally, I almost never use an inner join to an extension table for reporting purposes. If you do so, you will remove any records from your query that have no corresponding values in the extension table. I know that seems obvious, but I’ve helped many Admins who made that mistake and gotten erroneous report data back.

Tomorrow I’ll cover how to automatically populate your new extension table.

Related posts:

2 comments to The T-SQL Implications with FastForms Customizations

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>