Views can be very handy when you would like to pull data from two or more tables, for example, to see all customers that have placed an order.

For customers that have placed more than one order, performing a left join on customers will return multiple customers in that view.

Iron Speed can be configured to place Virtual Primary / Foreign Keys (VPK/VFK) on such objects but the problem can arise.....What do you then use as a VPK through Iron Speed?

Using customer will yield multiple results when using getrecord() in Iron Speed, so this cannot be done (as get record is only used to return a single result based on a primary key).

The solution I found quite elegant is to use the following SQL statement when designing a view:

 

Select Row_Number() over(order by dbo.customer.customerid) as 'Id'....(rest of the sql code ommitted)

 

What this does is, according to MSDN:

 

 "Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition."

 

When the sql is executed, the new "Id" column in the view begins at 1, then is incremented for each row that is returned specified by the order by clause in the over arguments.

 

By doing this, you can guarantee a unique Id is generated for newly inserted rows and can then set a VPK / VFK from a view in Iron Speed!

 

Regards,

 

Ricci