Generate Custom Bill with multiple records in Crystal Report
Hello Experts,
I have developed an ERP System in C#.net.
My SQL select statement is following.
When I execute the above query I am getting following 2 rows
My query gives me all same columns in both result except dispatch quantity and rate per quantity
In above example
POProduct - dispatch quantity is 15
Test in Production - dispatch quantity is 20
When I pass this query to Report it shows me the following report. I am getting 2 pages in report
In both pages all fields are displaying same records except dispatch quantity
On first page it shows 1st material dispatch quantity and on second page it shows 2nd material dispatch quantity (Check the attached screen-shot).
I wanted to display all the dispatch quantity in front of the same materail
Please Help me.
I have developed an ERP System in C#.net.
My SQL select statement is following.
string ViewBill= "SELECT CustomerCompanyDetails.CustomerCompanyName, GeneratedBill.ChargableInText, TaxationDetails.ECCNumber, TaxationDetails.TINNumber, ustomerCompanyDetails.CustomerCompanyAddress, CustomerCompanyDetails.PostalCode, tblPostalCodes.Municipality, tblPostalCodes.StateProvince, CustomerCompanyDetails.CustomerCode, GeneratedBill.PurchaseOrderNumber, PurchaseOrderClient.DateOfIssue1, CustomerCompanyDetails.CreditPeriod, CustomerCompanyDetails.CustomerCompanyID, PurchaseOrderClient.PODescription, GeneratedBill.MaterialName, PurchaseOrderClient.ratePerQty, PurchaseOrderClient.MaterialName, PODescription, ratePerQty, MaterialName2, PODescription2, ratePerQty2, GeneratedBill.DiaCharges, GeneratedBill.PaidExciseDuty, GeneratedBill.PaidEducation, GeneratedBill.PaidSHECESS, GeneratedBill.DutyAmount, GeneratedBill.OutPutVatorCST, GeneratedBill.ChargableAmount, GeneratedBill.BillNumberFromClient, GeneratedBill.DispatchQuantity, GeneratedBill.GeneratedBillDate
FROM CustomerCompanyDetails INNER JOIN PurchaseOrderClient ON CustomerCompanyDetails.CustomerCompanyID = PurchaseOrderClient.CustomerCompanyID INNER JOIN tblPostalCodes ON CustomerCompanyDetails.PostalCode = tblPostalCodes.PostalCode INNER JOIN GeneratedBill ON PurchaseOrderClient.PurchaseOrderNumber = GeneratedBill.PurchaseOrderNumber INNER JOIN TaxationDetails ON CustomerCompanyDetails.CustomerCompanyID = TaxationDetails.CustomerCompanyID
WHERE (GeneratedBill.PurchaseOrderNumber = '" + PONumber + "') AND (GeneratedBill.BillNumberFromClient = '" + BillNumber + "')";When I execute the above query I am getting following 2 rows
My query gives me all same columns in both result except dispatch quantity and rate per quantity
In above example
POProduct - dispatch quantity is 15
Test in Production - dispatch quantity is 20
When I pass this query to Report it shows me the following report. I am getting 2 pages in report
In both pages all fields are displaying same records except dispatch quantity
On first page it shows 1st material dispatch quantity and on second page it shows 2nd material dispatch quantity (Check the attached screen-shot).
I wanted to display all the dispatch quantity in front of the same materail
Please Help me.
ASKER
Hi James,
thanks for reply.
You are right. I want the 20 which is on 2nd page. Should be on the first page along with "Test in Production".
Can you provide the solution ??
thanks for reply.
You are right. I want the 20 which is on 2nd page. Should be on the first page along with "Test in Production".
Can you provide the solution ??
Unless there is some duplication of data in the report, I can only think you have the report grouped in some way that causes this.
mlmcc
mlmcc
ASKER
Hi mlmcc,
yes there is a duplication of data. means Query return 2 rows of records which contains some duplicate data except dispatch quantity. Check the first attached screen-shot
And I didn't grouped anything in the report. I am just passing the query to the report and I had designed the report by dragging the Fields from that Dataset Table to the report.
yes there is a duplication of data. means Query return 2 rows of records which contains some duplicate data except dispatch quantity. Check the first attached screen-shot
And I didn't grouped anything in the report. I am just passing the query to the report and I had designed the report by dragging the Fields from that Dataset Table to the report.
I can't tell from the query where the duplication is coming from.
mlmcc
mlmcc
As I said before, you may need to post the actual report (the RPT file). Without seeing the report structure, etc., it may be impossible to tell why you're getting those results (although someone could always make a lucky guess).
FWIW, you've said that there is "duplication", in that the 2 rows produced by the query are almost identical, but, to me, the real duplication is that you seem to be seeing those 2 lines on the report twice, on page 1 and sort of repeated on page 2, and without knowing more about the report, I've no idea why that is happening.
James
FWIW, you've said that there is "duplication", in that the 2 rows produced by the query are almost identical, but, to me, the real duplication is that you seem to be seeing those 2 lines on the report twice, on page 1 and sort of repeated on page 2, and without knowing more about the report, I've no idea why that is happening.
James
ASKER
OK, thanks for reply.
Currently i am not able to upload the report file. I Will post it tomorrow.
Sorry for late reply....
Currently i am not able to upload the report file. I Will post it tomorrow.
Sorry for late reply....
ASKER
The problem is you put everything in the detail section so it displays for each detail record.
Why do you have 7 materialname, description and rate fields but only 1 quantity field?
mlmcc
Why do you have 7 materialname, description and rate fields but only 1 quantity field?
mlmcc
You need to group on the PO Number field and put the common information in the group header like the address information.
The details should have the first line of detail information and the other 6 can be deleted.
mlmcc
The details should have the first line of detail information and the other 6 can be deleted.
mlmcc
ASKER
Hello mlmcc,
There are maximum 7 different material having description and rate in one table and the quantity is the dispatch quantity which is stored in another table.
i.e I can add max. 7 material in single purchase order. And while dispatching the material I can dispatch the materials which are ready and is between these 7 materials. So I am storing the dispatch quantity in different table.
As you say that other 6 can be deleted, I can't do that because there are 7 different columns for materials like MaterialName1, MaterialName2, MaterialName3........Mater
I am stuck here. ...!!
There are maximum 7 different material having description and rate in one table and the quantity is the dispatch quantity which is stored in another table.
i.e I can add max. 7 material in single purchase order. And while dispatching the material I can dispatch the materials which are ready and is between these 7 materials. So I am storing the dispatch quantity in different table.
As you say that other 6 can be deleted, I can't do that because there are 7 different columns for materials like MaterialName1, MaterialName2, MaterialName3........Mater
I am stuck here. ...!!
How many dispatch quantities could there be? Are they limited to 7 too, so there could be 1 for each of the 7 description/name fields? Or are they not connected? It looks like you want the quantity from the second record to show with the second set of description/name fields, implying that there is some kind of connection between them.
James
James
ASKER
Thanks,
Let me tell you the task flow.
1. I generate the Purchase Order with 3 materials like following
Purchase Order = 0001
Material Rate Quantity Description
1 Mat1 Rs. 100/- 40 Desc.1
2 Mat2 Rs. 400/- 30 Desc.2
3 Mat3 Rs. 300/- 20 Desc.3
Now, I have 2 Materials which are ready to dispatch lets say Mat1, and Mat2. But I can't send all the quantity due to size of material, or etc.
When I am generating the Bill, I will select only Mat1 and Mat2 and the quantity is 20 and 15 resp.
And the selected Materials are going to store in other table like GeneratedBill having dispatch quantity 20 and 15 with some additional information like BillNumber, ClientID, etc.
Have you got the flow.
or any confusion ???
Let me tell you the task flow.
1. I generate the Purchase Order with 3 materials like following
Purchase Order = 0001
Material Rate Quantity Description
1 Mat1 Rs. 100/- 40 Desc.1
2 Mat2 Rs. 400/- 30 Desc.2
3 Mat3 Rs. 300/- 20 Desc.3
Now, I have 2 Materials which are ready to dispatch lets say Mat1, and Mat2. But I can't send all the quantity due to size of material, or etc.
When I am generating the Bill, I will select only Mat1 and Mat2 and the quantity is 20 and 15 resp.
And the selected Materials are going to store in other table like GeneratedBill having dispatch quantity 20 and 15 with some additional information like BillNumber, ClientID, etc.
Have you got the flow.
or any confusion ???
I'm still wondering if those quantity records and the seven sets of fields are connected in any way. In your sample report, you seem to have values in two of the sets of description/name fields (eg. PODescription and PODescription2), and two quantity records. But it sounds like those numbers don't have to match. What if there are fewer quantity records than there are descriptions? Just show the "extra" descriptions without quantities? Could you have more than seven quantity records? If so, where do you want to show the "extra" quantities? Do they just go on lines by themselves with no description, etc.?
Also, when there are multiple quantity records, are those description/name fields the same in each record? For example, if there are 2 quantity records, is PODescription the same in every record, PODescription2 the same in every record, etc., or could they be different? From your sample report output, it looks like some of those fields may be the same, but some are different, in which case there may be a specific set of fields that should be displayed with each quantity record.
James
Also, when there are multiple quantity records, are those description/name fields the same in each record? For example, if there are 2 quantity records, is PODescription the same in every record, PODescription2 the same in every record, etc., or could they be different? From your sample report output, it looks like some of those fields may be the same, but some are different, in which case there may be a specific set of fields that should be displayed with each quantity record.
James
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Not understood mlmcc,
How to link it 7times. Can you edit the above Query, in that I have already linked these 2 tables i.e
PurchaseOrderClient and other is GeneratedBill
PurchaseOrderClient contains PONumber, CustomerID, MaterialName, MaterialName1,.......Mater
GeneratedBill contains DispatchQuantity, PONumber, BillNumber, BillTotal, etc.
How to link it 7times. Can you edit the above Query, in that I have already linked these 2 tables i.e
PurchaseOrderClient and other is GeneratedBill
PurchaseOrderClient contains PONumber, CustomerID, MaterialName, MaterialName1,.......Mater
GeneratedBill contains DispatchQuantity, PONumber, BillNumber, BillTotal, etc.
You can include the same table in a report more than once. CR will assign a different alias to each occurrence of the same table (eg. GeneratedBill, GeneratedBill_1, GeneratedBill_2), so that you can reference each of them separately in the report.
I _think_ what mlmcc was suggesting is that you link to the "dispatch table" (GeneratedBill?) once for each set of repeated fields (PODescription, PODescription2, etc.), so that you get a separate record for each set of fields. I don't know if that would solve the problem, or if it's even possible. But I think that that's what he was suggesting. In order to do that, I guess the repeated fields would have to include something that could be used to link each set of fields to a specific "dispatch table" record.
James
I _think_ what mlmcc was suggesting is that you link to the "dispatch table" (GeneratedBill?) once for each set of repeated fields (PODescription, PODescription2, etc.), so that you get a separate record for each set of fields. I don't know if that would solve the problem, or if it's even possible. But I think that that's what he was suggesting. In order to do that, I guess the repeated fields would have to include something that could be used to link each set of fields to a specific "dispatch table" record.
James
That is what I was suggesting. Since the report is based on a database query/view you will have to update it in the database.
I believe any oher method will require you to rebuild the report.
mlmcc
I believe any oher method will require you to rebuild the report.
mlmcc
ASKER
Sorry experts for late reply,
I tried the above solutions but still not getting proper result.. any other way ??
I tried the above solutions but still not getting proper result.. any other way ??
As I understand it:
A single PurchaseOrderClient record can have multiple GeneratedBill records.
PurchaseOrderClient has some fields (eg. MaterialName) that are repeated 7 times (eg. MaterialName, MaterialName1, MaterialName2, etc.).
You want to somehow "connect" the GeneratedBill records to those repeated fields, so that the DispatchQuantity (Only that field?) from the first GeneratedBill record is shown with the first set of repeated fields (eg. MaterialName), the DispatchQuantity from the second GeneratedBill record is shown with the second set of repeated fields (eg. MaterialName1), and so on.
Does all of that sound correct?
If so ...
Is there something, like a line number field, that associates a GeneratedBill record with a specific set of those repeated PurchaseOrderClient fields (MaterialName, MaterialName1, etc.)? If not, how do you know which GeneratedBill record to use on each line?
This might be answered by your answer to the question above, but how many GeneratedBill records can there be for a single PurchaseOrderClient record? The fields in PurchaseOrderClient are repeated 7 times. Can you have more than 7 GeneratedBill records? If so, what do you want to do with the "extras"?
James
A single PurchaseOrderClient record can have multiple GeneratedBill records.
PurchaseOrderClient has some fields (eg. MaterialName) that are repeated 7 times (eg. MaterialName, MaterialName1, MaterialName2, etc.).
You want to somehow "connect" the GeneratedBill records to those repeated fields, so that the DispatchQuantity (Only that field?) from the first GeneratedBill record is shown with the first set of repeated fields (eg. MaterialName), the DispatchQuantity from the second GeneratedBill record is shown with the second set of repeated fields (eg. MaterialName1), and so on.
Does all of that sound correct?
If so ...
Is there something, like a line number field, that associates a GeneratedBill record with a specific set of those repeated PurchaseOrderClient fields (MaterialName, MaterialName1, etc.)? If not, how do you know which GeneratedBill record to use on each line?
This might be answered by your answer to the question above, but how many GeneratedBill records can there be for a single PurchaseOrderClient record? The fields in PurchaseOrderClient are repeated 7 times. Can you have more than 7 GeneratedBill records? If so, what do you want to do with the "extras"?
James
ASKER
Hi thanks for your reply..
You are right way.
Yes, single PurchaseOrderClient record can have multiple GeneratedBill records.
In my PurchaseOrderClient, I have limit of 7 materials. Means I can add maximum 7 materials in single Purchase Order. So I have 7 columns for that ( MaterialName, MaterialName1... MaterialName7 etc)
Consider the following scenario ,
PurchaseOrder Number is SONY2011_4 and in this PO there are 3 materials records like MaterialName, MaterialName1, MaterialName2, Quantity, Quantity1, Quantity2 etc.
Quantity = 30, Quantity1 = 40, Quantity3 = 50
Now I am dispatching some Quantity of these materials in GeneratedBill table
I can select any material and dispatch it. See the UI screen.
In this I am creating Bill for selected material with quantity 20. So I am inserting records in GeneratedBill table using for loop function like following
INSERT INTO GeneratedBill (SaleType, CustomerCompanyName, PurchaseOrderNumber, DiaCharges, PaidExciseDuty, PaidEducation, PaidSHECESS, DutyAmount, OutPutVatorCST, ChargableAmount, BillNumberFromClient, ChargableInText, MaterialName, DispatchQuantity, RemainingQty, POOriginalQty, MaterialNumberForDescrptio
Now I have 2 records in GeneratedBill table for selected 2 materials as below
MaterialName DispatchQuantity RemainingQty POOriginalQty BillNumberFromClient PurchaseOrderNumber
NEW INSERT 20 0 30 00012 SONY2011_4
TEST PRODUCT 20 20 50 00012 SONY2011_4
Now,
when I collect the records from these tables I am getting 2 rows with dispatch quantity 20. But in .rpt file it shows single dispatch quantity on one page and for second record, it takes second page
You are right way.
Yes, single PurchaseOrderClient record can have multiple GeneratedBill records.
In my PurchaseOrderClient, I have limit of 7 materials. Means I can add maximum 7 materials in single Purchase Order. So I have 7 columns for that ( MaterialName, MaterialName1... MaterialName7 etc)
Consider the following scenario ,
PurchaseOrder Number is SONY2011_4 and in this PO there are 3 materials records like MaterialName, MaterialName1, MaterialName2, Quantity, Quantity1, Quantity2 etc.
Quantity = 30, Quantity1 = 40, Quantity3 = 50
Now I am dispatching some Quantity of these materials in GeneratedBill table
I can select any material and dispatch it. See the UI screen.
In this I am creating Bill for selected material with quantity 20. So I am inserting records in GeneratedBill table using for loop function like following
INSERT INTO GeneratedBill (SaleType, CustomerCompanyName, PurchaseOrderNumber, DiaCharges, PaidExciseDuty, PaidEducation, PaidSHECESS, DutyAmount, OutPutVatorCST, ChargableAmount, BillNumberFromClient, ChargableInText, MaterialName, DispatchQuantity, RemainingQty, POOriginalQty, MaterialNumberForDescrptio
Now I have 2 records in GeneratedBill table for selected 2 materials as below
MaterialName DispatchQuantity RemainingQty POOriginalQty BillNumberFromClient PurchaseOrderNumber
NEW INSERT 20 0 30 00012 SONY2011_4
TEST PRODUCT 20 20 50 00012 SONY2011_4
Now,
when I collect the records from these tables I am getting 2 rows with dispatch quantity 20. But in .rpt file it shows single dispatch quantity on one page and for second record, it takes second page
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks James,
I followed your solutions and it shows me the output as I want. but I am facing a little bit new problem that.
When I suppress the sections, it gives me following output.
It does not show any material. Means the detail section part is hiding completely.
And without suppress the sections -
It repeats the Data in each sections
You can Check my .rpt files
crptClientBill-new.rpt
I followed your solutions and it shows me the output as I want. but I am facing a little bit new problem that.
When I suppress the sections, it gives me following output.
It does not show any material. Means the detail section part is hiding completely.
And without suppress the sections -
It repeats the Data in each sections
You can Check my .rpt files
crptClientBill-new.rpt
Can you save the data with the report?
mlmcc
mlmcc
ASKER
No I am just Displaying the data.
WHen you edit a report there is an option to SAVE DATA WIH REPORT. If you check that then save you will have the data in the report.
mlmcc
mlmcc
ASKER
Hi mlmcc, I have seen that option. it is unchecked means I am not saving any data in Report.....
Can you check the option
Save the report and upload it?
mlmcc
Save the report and upload it?
mlmcc
In case you're still not following, mlmcc was asking if you could use the "save data" option, save the report with some data, and u/l that, so that we can see the actual report output, look at the saved data, and maybe try altering something in the report and see how the output changes. Of course if the data includes confidential/sensitive information, then that's not an option, but since you've posted screenshots of the report output, that doesn't seem to be a problem (unless there's something confidential in fields that aren't visible on the report, since those fields could still be included in the saved data).
James
James
ASKER
ok, I have checked the option and save it !!
Checked the attached report
crptClientBill-new.rpt
Class file ( if needed)
crptClientBill-new.cs
Checked the attached report
crptClientBill-new.rpt
Class file ( if needed)
crptClientBill-new.cs
There is no data in the report when I open it.
mlmcc
mlmcc
ASKER
How is this possible. I have saved the data.. !!
Check below file. I have export this file while executing the report from Export option. Is this ok ??
ClientBillOutPut.rpt
Check below file. I have export this file while executing the report from Export option. Is this ok ??
ClientBillOutPut.rpt
WHy are you grouping on Quantity?
Does the database have 3 records with the only difference being the quantity?
Is this a table or a view from the database?
mlmcc
Does the database have 3 records with the only difference being the quantity?
Is this a table or a view from the database?
mlmcc
Once you preview the report all you need to do is SAVE it with the option SAVE DATA WITH REPORT checked.
mlmcc
mlmcc
ASKER
Hi mlmcc,
Yes, In one table, I have 3 records with only different quantity ( I am fetching data from different 4 tables for getting Client details, etc). This is the table not a View. I followed the James solution to add section for every different material.
And I have already checked this option to SAVE DATA WITH REPORT. See the Screen Shot.
Yes, In one table, I have 3 records with only different quantity ( I am fetching data from different 4 tables for getting Client details, etc). This is the table not a View. I followed the James solution to add section for every different material.
And I have already checked this option to SAVE DATA WITH REPORT. See the Screen Shot.
The report is showing what it should. What do you not want to see?
mlmcc
mlmcc
ASKER
Yes, the report is showing the result.
But, the materials are repeated. Means, in previous comment when I show the bill with 3 materials it shows the exact quantity for different material i.e 20, 30, 40. But I am getting the material, description and rate/quantity 3 times.
See below image.
But, the materials are repeated. Means, in previous comment when I show the bill with 3 materials it shows the exact quantity for different material i.e 20, 30, 40. But I am getting the material, description and rate/quantity 3 times.
See below image.
How do you know they are repeating?
Did you try using the SUPPRESS IF DUPLICATED option in the FORMAT FIELD --> COMMON TAB?
mlmcc
Did you try using the SUPPRESS IF DUPLICATED option in the FORMAT FIELD --> COMMON TAB?
mlmcc
I don't really think it's this simple, but in your screenshot, you seem to be saying that you don't want to see those lines at all. If that's really true, just suppress DetailsB and DetailsC (and presumably DetailsD through DetailsG too).
However, I'm guessing that you do want to see the description and rate/quantity from those lines that you marked out. If so ...
It looks like for each GeneratedBill record, MaterialName is changed, so it's different in each record ("NEW INSERT", "POPRD" and "TEST PRODUCT"). In the second record, MaterialName matches MaterialName2 ("POPRD"). In the third record, MaterialName matches MaterialName3 ("TEST PRODUCT").
However, the PODescription and ratePerQty fields (the first fields in each set) aren't changed like that. They're the same in every record. PODescription is "NEW INSERTNEW INSERTNEW INSERT" in each record, and ratePerQty is 30 in each record. If they were changed like MaterialName, then you might not have a problem. You'd just use MaterialName, PODescription and ratePerQty (the first set of fields) on the report, and forget the other fields (2 - 7).
I'm guessing that you want to see PODescription2 and ratePerQty2 with the second record ("POPRD"), and PODescription3 and ratePerQty3 with the third record ("TEST PRODUCT"), and the same for PODescription4, etc., if there were more GeneratedBill records.
Which brings us back to the question of how the report will know which record it's on, so it knows which set of fields to use.
First of all, I noticed that the last report that you posted, with the saved data, only includes the GeneratedBill table. Are you not using any other tables anymore, or was that just for testing purposes? If the final report is going to include other tables, that might affect the solution.
As mlmcc asked earlier, why is that report grouped on DispatchQuantity? That just seems odd, and if the group is going to change, that could affect the solution.
As for the report knowing which record it's on, so it knows which fields to use, the simplest thing would be if there were some kind of line number field. On line number 1, the report would use MaterialName, PODescription and ratePerQty. On line number 2, the report would use MaterialName2, PODescription2 and ratePerQty2. And so on.
If there is no line number field, another option might be to check the MaterialName fields. In your example, there are 3 different material names (MaterialName, MaterialName2 and MaterialName3 are different in the first record). Will the material names _always_ be different like that, or could the same name be used more than once (for example, if "POPRD" was used twice, in both MaterialName2 and MaterialName4)?
If the names will always be different, you could figure out which PODescription* and ratePerQty* fields to use by comparing MaterialName (the first field) with MaterialName2 through MaterialName7. If MaterialName = MaterialName2, you use PODescription2 and ratePerQty2. If MaterialName = MaterialName3, you use PODescription3 and ratePerQty3. And so on. But that only works if you don't use the same material name twice.
Otherwise, you could theoretically use a "line number" variable that is incremented for each record and use that to determine which fields to use with each record.
James
However, I'm guessing that you do want to see the description and rate/quantity from those lines that you marked out. If so ...
It looks like for each GeneratedBill record, MaterialName is changed, so it's different in each record ("NEW INSERT", "POPRD" and "TEST PRODUCT"). In the second record, MaterialName matches MaterialName2 ("POPRD"). In the third record, MaterialName matches MaterialName3 ("TEST PRODUCT").
However, the PODescription and ratePerQty fields (the first fields in each set) aren't changed like that. They're the same in every record. PODescription is "NEW INSERTNEW INSERTNEW INSERT" in each record, and ratePerQty is 30 in each record. If they were changed like MaterialName, then you might not have a problem. You'd just use MaterialName, PODescription and ratePerQty (the first set of fields) on the report, and forget the other fields (2 - 7).
I'm guessing that you want to see PODescription2 and ratePerQty2 with the second record ("POPRD"), and PODescription3 and ratePerQty3 with the third record ("TEST PRODUCT"), and the same for PODescription4, etc., if there were more GeneratedBill records.
Which brings us back to the question of how the report will know which record it's on, so it knows which set of fields to use.
First of all, I noticed that the last report that you posted, with the saved data, only includes the GeneratedBill table. Are you not using any other tables anymore, or was that just for testing purposes? If the final report is going to include other tables, that might affect the solution.
As mlmcc asked earlier, why is that report grouped on DispatchQuantity? That just seems odd, and if the group is going to change, that could affect the solution.
As for the report knowing which record it's on, so it knows which fields to use, the simplest thing would be if there were some kind of line number field. On line number 1, the report would use MaterialName, PODescription and ratePerQty. On line number 2, the report would use MaterialName2, PODescription2 and ratePerQty2. And so on.
If there is no line number field, another option might be to check the MaterialName fields. In your example, there are 3 different material names (MaterialName, MaterialName2 and MaterialName3 are different in the first record). Will the material names _always_ be different like that, or could the same name be used more than once (for example, if "POPRD" was used twice, in both MaterialName2 and MaterialName4)?
If the names will always be different, you could figure out which PODescription* and ratePerQty* fields to use by comparing MaterialName (the first field) with MaterialName2 through MaterialName7. If MaterialName = MaterialName2, you use PODescription2 and ratePerQty2. If MaterialName = MaterialName3, you use PODescription3 and ratePerQty3. And so on. But that only works if you don't use the same material name twice.
Otherwise, you could theoretically use a "line number" variable that is incremented for each record and use that to determine which fields to use with each record.
James
ASKER
Sorry Experts for delaying the response for the question. I was assigned to other Project. But now I think I need to finished this topic.
Will get back to this page with trying last commented solution from James.
Thank you & Sorry again...
Will get back to this page with trying last commented solution from James.
Thank you & Sorry again...
ASKER
Around the solution.....
ASKER
Trying the solution now....
Thanks for your time...
Thanks for your time...
ASKER
Hello Experts,
Thanks for your opinion and time. Really appreciate your work and efforts. This question is not suppose to delete without your appreciation. I really Don't want to waste your efforts. That's why I am accepting this solution.
Will get back with this question after sometime....
Really THANKS
Thanks for your opinion and time. Really appreciate your work and efforts. This question is not suppose to delete without your appreciation. I really Don't want to waste your efforts. That's why I am accepting this solution.
Will get back with this question after sometime....
Really THANKS
You may need to post the report (RPT file). Without seeing the report structure, etc., I have no idea why it might be giving you those 2 pages.
James