romeo Ballesteros
asked on
PO Form in Crystal Report,, details duplicates
HI I am using Crystal report 2016, I am getting my Database thru SQL server and application is Prism G2.
I have 2 Tables PO_Item (itemized), Purchase_Order (Summary with Vendor and other terms)
I get my PO details in PO_Items and insert it to the detail section. When ever I insert a field PO_items.PO_ID in the report Header I am getting the correct preview of all the items in a particular PO. I need to display the Terms, vendor, vendor address to the report header as well and will get it from the table Purchase_order. Once I insert the filed vendor, vendor address and terms to the report header from table Purchase_Order my details are getting hay wire, it multiplies the number of filed I inserted. I know that adding a field to the Report header form another Purchase Order table creates the multiplying of items because when I deleted the fields from the report header it goes back to the correct preview.
The thing is I need the Vendor, Vendor Address the Terms and delivery terms in my PO Form, but this causes the problem. How do I correct this.
Is there a way to make this field read only or use this field but will not affect the details.
I did not use any linkage from one table to another, because if I do auto Link, the PO will be blank.
Can somebody explain to me the principle of Linking Tables and tips on how to link table properly (what to remember and to do etc...)
One last thing is the first page of my PO form is the Purchase order Cover form which is positioned to the whole Report Header, my problem is it does not show the footer, I thought it was just the margin but when I lowered the margin in only shows on the succeeding page, did I missed something? Please help...
Thanks.
Best regards
Romeo Ballesteros
I have 2 Tables PO_Item (itemized), Purchase_Order (Summary with Vendor and other terms)
I get my PO details in PO_Items and insert it to the detail section. When ever I insert a field PO_items.PO_ID in the report Header I am getting the correct preview of all the items in a particular PO. I need to display the Terms, vendor, vendor address to the report header as well and will get it from the table Purchase_order. Once I insert the filed vendor, vendor address and terms to the report header from table Purchase_Order my details are getting hay wire, it multiplies the number of filed I inserted. I know that adding a field to the Report header form another Purchase Order table creates the multiplying of items because when I deleted the fields from the report header it goes back to the correct preview.
The thing is I need the Vendor, Vendor Address the Terms and delivery terms in my PO Form, but this causes the problem. How do I correct this.
Is there a way to make this field read only or use this field but will not affect the details.
I did not use any linkage from one table to another, because if I do auto Link, the PO will be blank.
Can somebody explain to me the principle of Linking Tables and tips on how to link table properly (what to remember and to do etc...)
One last thing is the first page of my PO form is the Purchase order Cover form which is positioned to the whole Report Header, my problem is it does not show the footer, I thought it was just the margin but when I lowered the margin in only shows on the succeeding page, did I missed something? Please help...
Thanks.
Best regards
Romeo Ballesteros
Smart Linking (Auto Link) has never worked well in Crystal. I always turn it off.
To it to work properly you need to link the tables other wise Crystal will join each record in table 1 to every record in table 2.
Generally you want to link the key fields of the 2 tables. In your case you probably have a PO Number field in the PO Item table. Join on that field
Add a group on the purchase order and put the common information into the group.
mlmcc
To it to work properly you need to link the tables other wise Crystal will join each record in table 1 to every record in table 2.
Generally you want to link the key fields of the 2 tables. In your case you probably have a PO Number field in the PO Item table. Join on that field
Add a group on the purchase order and put the common information into the group.
mlmcc
ASKER
Hi,
Thank you very much for your valuable comments, both of you comments helped a lot and it solved the duplication problem. The only thing left is the first page page numbering. I have increased the page footer space but still no page displayed in the first page. If I give it to much space part of the details will be displayed then a page footer will appear.
In Crystal report is it only on the detail section will display page footer? Or will it also display page footer if you use the whole Report Header as your first page? Is there a paging parameter which you turn off page footer in the beginning of the page?
Thanks.
Romeo
Thank you very much for your valuable comments, both of you comments helped a lot and it solved the duplication problem. The only thing left is the first page page numbering. I have increased the page footer space but still no page displayed in the first page. If I give it to much space part of the details will be displayed then a page footer will appear.
In Crystal report is it only on the detail section will display page footer? Or will it also display page footer if you use the whole Report Header as your first page? Is there a paging parameter which you turn off page footer in the beginning of the page?
Thanks.
Romeo
ASKER
I have a quick question again, I created Report header A & B. In Header A is the Top Header showing Form Title Logo and Job No. and Job Name and I want this shown in all pages.
Header B are the details of the Purchaser Order such as the Vendor, Terms, Attachments, Signature box etc.. and I want this shown only in the first page.
I cannot show Report Header A on all the pages, I only need Report Header A shown in all pages. How do I do this?
Thanks.
Romeo
Header B are the details of the Purchaser Order such as the Vendor, Terms, Attachments, Signature box etc.. and I want this shown only in the first page.
I cannot show Report Header A on all the pages, I only need Report Header A shown in all pages. How do I do this?
Thanks.
Romeo
The report header is just that the first part of the report and it is shown only once. There is a page header that is available for all pages.
The page footer does not show in the report header.
mlmcc
The page footer does not show in the report header.
mlmcc
Put Header A in the page header. For Header B, you could do as mlmcc suggested earlier and create a group on the PO # and then put Header B in the group header. By default, the group header is shown once for each group.
James
James
ASKER
Thank you very much James and MLMCC.
I think one last thing I need is, I have a Table of Addresses, these addresses are used where to send official communication about a PO, address where to send the Invoice and where to deliver the items. The issue is instead of the address appearing to the PO the Address ID is the one showing in the PO.
Example:
Table Address
Address_ID = COMM2S
Address_Detail = XYZ Company
Attention: David L
20914 72 St. Dowson
Vancouver, Canada
XYZ123
Davidl@gmail.com
What do you guys think triggering this issue, showing ID instead of the Detail.
By the way, the ID comes from ad different Table which Purchase_order Table and the address database from another table which is Delivery_Address Table
Thanks.
Romeo
I think one last thing I need is, I have a Table of Addresses, these addresses are used where to send official communication about a PO, address where to send the Invoice and where to deliver the items. The issue is instead of the address appearing to the PO the Address ID is the one showing in the PO.
Example:
Table Address
Address_ID = COMM2S
Address_Detail = XYZ Company
Attention: David L
20914 72 St. Dowson
Vancouver, Canada
XYZ123
Davidl@gmail.com
What do you guys think triggering this issue, showing ID instead of the Detail.
By the way, the ID comes from ad different Table which Purchase_order Table and the address database from another table which is Delivery_Address Table
Thanks.
Romeo
You're using ADDRESS_COM_ID there. Presumably you need to add the Delivery_Address table to the report (assuming that it's not already included) and use ADDRESS_COM_ID to link to that table, and then use the address fields from Delivery_Address.
James
James
ASKER
Thanks James, for the link, is it from address_ID to Delivery_address.address field or vice versa?
The latter is already in the report and when I used the address field to the PO form from the delivery_address table my detail data multiplied by 60 times.
Do i need to link project ID or primary key table. Whats the use of linking primary keys?
Thanks.
Romeo
The latter is already in the report and when I used the address field to the PO form from the delivery_address table my detail data multiplied by 60 times.
Do i need to link project ID or primary key table. Whats the use of linking primary keys?
Thanks.
Romeo
You would want to link from the PO tables to Delivery_Address. Use whichever PO table (probably Purchase_Order) has the field(s) that you need to get the correct record in Delivery_Address (I'm assuming that the address is contained in a single record in Delivery_Address).
If your data was duplicated when you used a field from Delivery_Address, then you probably used the wrong field(s) to link Delivery_Address, or didn't link it at all (in which case you would get an extra record for every record in Delivery_Address).
I'm not sure what you mean by "primary key table". Do you actually have a table with just primary keys?
If you're just asking if you should use a primary key to link tables, the answer is what I said before:
Use whichever fields are required to get the correct Delivery_Address record.
If you're linking to a primary key in Delivery_Address, that's a plus, because primary keys should be indexed, so access using that key is faster/more efficient. But it doesn't help if you don't have an equivalent field in the table that you're linking from. For example, if Delivery_Address had a simple number (eg. 1, 2, 3) as the primary key, but Purchase_Order had a code (eg. "Acme1", "Acme2"), then you couldn't use the primary key for the link. You would have to use the field in Delivery_Address that contained those codes.
James
If your data was duplicated when you used a field from Delivery_Address, then you probably used the wrong field(s) to link Delivery_Address, or didn't link it at all (in which case you would get an extra record for every record in Delivery_Address).
Do i need to link project ID or primary key table. Whats the use of linking primary keys?Use whichever fields are required to get the correct Delivery_Address record.
I'm not sure what you mean by "primary key table". Do you actually have a table with just primary keys?
If you're just asking if you should use a primary key to link tables, the answer is what I said before:
Use whichever fields are required to get the correct Delivery_Address record.
If you're linking to a primary key in Delivery_Address, that's a plus, because primary keys should be indexed, so access using that key is faster/more efficient. But it doesn't help if you don't have an equivalent field in the table that you're linking from. For example, if Delivery_Address had a simple number (eg. 1, 2, 3) as the primary key, but Purchase_Order had a code (eg. "Acme1", "Acme2"), then you couldn't use the primary key for the link. You would have to use the field in Delivery_Address that contained those codes.
James
ASKER
Hi I have another question, is it possible to insert a sequence number formula in a form?
Can you please let me know how to do it?
Thanks.
Romeo
Can you please let me know how to do it?
Thanks.
Romeo
Did you get the delivery address working? Just checking.
What are you trying to number? Pages, PO's, lines/records, or something else? CR has built-in items to display the page and record number. You can find those under Special Fields in the Field Explorer. Another option might be to use a running total and make it a count. Or you can use formulas with a variable.
James
What are you trying to number? Pages, PO's, lines/records, or something else? CR has built-in items to display the page and record number. You can find those under Special Fields in the Field Explorer. Another option might be to use a running total and make it a count. Or you can use formulas with a variable.
James
ASKER
Not yet, I'm still getting the same result displaying Address ID rather than the Address it-self. I was thinking to create a Look up formula same as excel (vlookup(Address_ID="COMM2 ",Field where to lookup (DELIVERY_ADDRESS.ADDRESS) , No of column, FALSE), is that possible or maybe match formula?
I just want to display the address detail of an assigned Address ID.
Going back on my last question, I want to number an attachment to a PO which I want to get the first 16 Character of the PO No. (NBT002A6-PC-CAF-) and then add the 4 character number sequence at the last part so it will show as "NBT002A6-PC-CAF-0001" next will be "NBT002A6-PC-CAF-0002".
Thanks.
Romeo
I just want to display the address detail of an assigned Address ID.
Going back on my last question, I want to number an attachment to a PO which I want to get the first 16 Character of the PO No. (NBT002A6-PC-CAF-) and then add the 4 character number sequence at the last part so it will show as "NBT002A6-PC-CAF-0001" next will be "NBT002A6-PC-CAF-0002".
Thanks.
Romeo
Forgive me for stating the obvious, but it's showing the address ID because that's the field that you put on the report. You need to use the appropriate fields from the Delivery_Address table. Use the Address ID field to link one of the PO tables to Delivery_Address, and then put the required address fields on the report. It should be that simple (but things don't always go the way that they should :-).
As for the numbering, since the first part of the "number" comes from the PO #, I assume that there is only one set of attachments for each PO (so you only need one number sequence per PO).
There are at least a couple of ways that you could handle that. The easiest might be a running total. Right-click on the PO# field (or most any field on the report will probably do) and select Insert > Running Total. Make the summary a count, evaluated for each record, and set to reset when the PO # changes (the reset isn't necessary if the report is only run for one PO at a time). Name the running total whatever you like.
Then create a formula like the following to output the sequence:
Left ({your PO # field}, 16) + CStr ({#your running total}, "0000")
James
As for the numbering, since the first part of the "number" comes from the PO #, I assume that there is only one set of attachments for each PO (so you only need one number sequence per PO).
There are at least a couple of ways that you could handle that. The easiest might be a running total. Right-click on the PO# field (or most any field on the report will probably do) and select Insert > Running Total. Make the summary a count, evaluated for each record, and set to reset when the PO # changes (the reset isn't necessary if the report is only run for one PO at a time). Name the running total whatever you like.
Then create a formula like the following to output the sequence:
Left ({your PO # field}, 16) + CStr ({#your running total}, "0000")
James
ASKER
Thanks James, Actually what I am creating is a commitment Authorization form which we create before approval of a Purchase Order and this is numbered in sequence, numbering the document is just one of my problem, another is since this is just a form which doesn't require details (as in I did not use the PO_item Table were the cost and qty of the PO is store). But it requires the total Cost of a particular PO. Now I cannot get the total PO because I cannot create the Running Total Formula (since I suppress Detail or did not use the detail section). Normally if I use the Detail section and insert the Qty and the Cost a "Running Total Field appear on the Field Explorer.
How can I show the Total of a PO if Detail section is not used? Can I get a total from the separate PO Report? How?
And this report I only want to show three pages, Report Header a & B which shows the detail of the commitment form and the report footer which shows the signatories. If I suppress the Detail section, it shows blank ao all the fields I used and If I un suppress it it shows all the field I used but the number of pages increases up to the number of details I have in the database.
Thanks again.
Romeo
How can I show the Total of a PO if Detail section is not used? Can I get a total from the separate PO Report? How?
And this report I only want to show three pages, Report Header a & B which shows the detail of the commitment form and the report footer which shows the signatories. If I suppress the Detail section, it shows blank ao all the fields I used and If I un suppress it it shows all the field I used but the number of pages increases up to the number of details I have in the database.
Thanks again.
Romeo
You should be able to get a summary or running total even if the detail section is suppressed. You have to use a field from the PO_Item table to get CR to include that table, but presumably the field that you're trying to summarize is in that table, so you'll be using that field for the summary, if nothing else.
If you're still having problems, it might help if you could post the report (the .RPT file), so that we can take a look at it.
James
If you're still having problems, it might help if you could post the report (the .RPT file), so that we can take a look at it.
James
ASKER
Hi James,
How can I send the rpt file to you?
Thanks.
Romeo
How can I send the rpt file to you?
Thanks.
Romeo
You can upload it here as an attachment.
Has your initial question been resolved?
If so it may be time to close this and ask a new question
mlmcc
Has your initial question been resolved?
If so it may be time to close this and ask a new question
mlmcc
ASKER
Hi part of my issues are solved, there are part that I still can't get it right.
Attached is the report file.
Thanks.
Romeo
CAF.rpt
PO-Report-Crystal--3-.rpt
Attached is the report file.
Thanks.
Romeo
CAF.rpt
PO-Report-Crystal--3-.rpt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Appreciate all the help. Woukd not completed without tge assistance. Thanks.
You're welcome. Glad I could help.
James
James
If you're trying to link Purchase_Order and PO_Item, they should both have a PO # field. If you use that for the link and still get some duplicated items, then the link may need to include some other field(s).
I'm not sure what you're saying about the cover form in the report header. To start with, when you say "does not show the footer", are you talking about the page footer or report footer? I'm guessing page footer. My first guess would be that there isn't enough room for the page footer after the report header.
James