>>>>>>>>>Are you talking about view in SQL Server or VFP? And how would a view help in this case?
>>>>>>>>>
>>>>>>>>I was thinking of SQL Server view, although it will work for VFP as well. We don't want to have calculated field in a table using UDF (as we need to base calculation on another table). The better solution is to use a view where this will be calculated.
>>>>>>>
>>>>>>>I have never, yet, used views in SQL Server. I will make a point to read and learn. Right now I am trying to make my app work on either SQL Server or VFP, using pretty much the same table structures and code. Thank you.
>>>>>>
>>>>>>
>>>>>>Dmitry,
>>>>>>
>>>>>>This is an example how to add po_Freight and po_SalesTax calculated fields to PO, summing them from the invoice table
>>>>>>

>>>>>>	create cursor po ;
>>>>>>	(	po_id		I, ;
>>>>>>		po_vendor		c(30), ;
>>>>>>		po_date		D, ;
>>>>>>		po_amount		Y ;
>>>>>>	)
>>>>>>	
>>>>>>	insert into po values (1, 'UT', date(), $100)
>>>>>>	insert into po values (2, 'Google', date()+1, $200)
>>>>>>	
>>>>>>	insert into po values (3, 'Microsoft', date()+2, $200)
>>>>>>	
>>>>>>	create cursor invoice ;
>>>>>>	(	inv_id		I, ;
>>>>>>		inv_po_id		I, ;
>>>>>>		inv_Freight		Y, ;
>>>>>>		inv_SalesTax	Y ;
>>>>>>	)
>>>>>>	
>>>>>>	insert into invoice values(1, 1, $20, $20)
>>>>>>	insert into invoice values(2, 1, $20, $20)
>>>>>>	
>>>>>>	
>>>>>>	insert into invoice values(3, 2, $20, $20)
>>>>>>	insert into invoice values(4, 2, $30, $30)
>>>>>>	
>>>>>>	
>>>>>>	
>>>>>>	select	;
>>>>>>			po_id, ;
>>>>>>			po_vendor, ;
>>>>>>			po_date, ;
>>>>>>			po_amount, ;
>>>>>>			nvl(po_Freight, $0)		as po_Freight, ;
>>>>>>			nvl(po_SalesTax, $0)	as	 po_SalesTax;
>>>>>>		from po ;
>>>>>>			left join ;
>>>>>>				( select ;
>>>>>>						inv_po_id, ;
>>>>>>						sum(inv_Freight) as po_Freight, ;
>>>>>>						sum(inv_SalesTax) as po_SalesTax ;
>>>>>>					from invoice ;
>>>>>>					group by 1 ;
>>>>>>				) X;
>>>>>>				on ( po_id == inv_po_id) 
>>>>>>

>>>>>
>>>>>Gregory, thank you very much!
>>>>
>>>>
>>>>You're welcome. This would be the view. If only one PO is needed, you can add a parameter
>>>>
>>>>

>>>>	po_needed = 3
>>>>	
>>>>	
>>>>	select	;
>>>>			po_id, ;
>>>>			po_vendor, ;
>>>>			po_date, ;
>>>>			po_amount, ;
>>>>			nvl(po_Freight, $0)		as po_Freight, ;
>>>>			nvl(po_SalesTax, $0)	as	 po_SalesTax ;
>>>>		from po ;
>>>>			left join ;
>>>>				( select ;
>>>>						inv_po_id, ;
>>>>						sum(inv_Freight) as po_Freight, ;
>>>>						sum(inv_SalesTax) as po_SalesTax ;
>>>>					from invoice ;
>>>>					where	( inv_po_id == ?po_needed ) ;
>>>>					group by 1 ;
>>>>				) X ;
>>>>				on ( po_id == inv_po_id) ;
>>>>		where ( po_id == ?po_needed ) ;
>>>>
>>>>

>>>
>>>First, thank you. Also, do I understand that this is an example of view that can to be created in SQL Server and not in VFP, right?
>>
>>
>>Notice de semicolons ? It's vfp. But I see no reason why it cannot be done in sqlserver
>
>Thank you for clarifying. Sorry that I didn't pay attention to the semicolons. I will test this approach in both VFP and SQL Server. Initially I thought I would do it using a Function (with pretty much similar code that you wrote). But view is probably better approach (although I don't yet understand why).


> But view is probably better approach (although I don't yet understand why).


I think it is because a view is faster.

btw - I have used nvl() in case there are no invoices yet. I think the sqlserver equivalent is ISNULL() http://msdn.microsoft.com/en-us/library/ms184325.aspx