Hi all,

My problem is I am not able to insert a field getting data from query. I am using Crystal Report 7.0. I have tried it thru SQL expression fields.

The scenario is, I have 3 tables

itmmast(itemtype, itemsize, make, qtymtr)

grnmast(grnno, grndate)

grndetails(grnno, itemtype,itemsize,make, qty)

grnmast & grndetails are master-details table.

In itmmast, hierarchy is based on

itemtype---> itemsize--->make

In my report, I am getting records ordered by itemsize, irrespective of make. I am able to get such records.

Now the problem is I need to insert a field in record that pulls data from grndetails.

The query can be formalized as follows:

select sum(grndetails.qty) from grnmast, grndetails, itmmast where grnmast.grnno=grndetails.grnno and grndetails.itemsize= itmmast.itemsize and grnmast.grndate > mydate;

Now mydate depends on date for financial year 1st April to 31st March.
So if current date lies bet. jan to mar, it financial year is 1st April of previous year and if it is after march then its year will start from 1st April of same year.

I tried using formula fields but din't get correct values.
can anyone help me how I can work it out?