• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 89
  • Last Modified:

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
0
romeo Ballesteros
Asked:
romeo Ballesteros
  • 10
  • 9
  • 3
1 Solution
 
James0628Commented:
I did not use any linkage from one table to  another, because if I do auto Link, the PO will be blank.
It sounds like the auto link is including some fields that it shouldn't.  For example, if two tables have a field with the same name, but they actually store different values (eg. PO.Number is the PO # and Vendor.Number is the vendor ID).  In that case you need to remove those links and create the links manually.  Just click on a field in one table and drag it to the corresponding field in another table.  You just have to identify which fields are required to link the tables.

 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
0
 
mlmccCommented:
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
0
 
romeo BallesterosAuthor Commented:
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
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
romeo BallesterosAuthor Commented:
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
0
 
mlmccCommented:
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
0
 
James0628Commented:
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
0
 
romeo BallesterosAuthor Commented:
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

Snapshot of the PO showing the Field ID, instead of the Detail
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

Snapshot of the PO showing the Field ID, instead of the Detail
Snapshot of the two tables Delivery Address and Purchase Order (highlighted are the fields.
Thanks.

Romeo
0
 
James0628Commented:
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
0
 
romeo BallesterosAuthor Commented:
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
0
 
James0628Commented:
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).

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
0
 
romeo BallesterosAuthor Commented:
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
0
 
James0628Commented:
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
0
 
romeo BallesterosAuthor Commented:
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
0
 
James0628Commented:
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
0
 
romeo BallesterosAuthor Commented:
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
0
 
James0628Commented:
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
0
 
romeo BallesterosAuthor Commented:
Hi James,

How can I send the rpt file to you?

Thanks.

Romeo
0
 
mlmccCommented:
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
0
 
romeo BallesterosAuthor Commented:
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
0
 
James0628Commented:
Was there something that you wanted me to look at in PO-Report-Crystal--3-.rpt, or was that just to show some of the data that you're trying to use in CAF.rpt?

 As far as CAF goes, yeah, a running total won't work, because you're trying to show that figure in the report header.  Running totals are created as the records are read/processed, so you won't have a final total until the end of the report.  In the report header, you'll just get the result from the first record in the report.

 If you just want a simple total, you can use a summary instead (you can use a grand total summary at any point in the report).  If you want to total a field, one way would be to put the field in the detail section, then right-click on that field and select Insert > Summary.  That will create a summary field in the report footer, but you can just drag&drop to move it to the report header.  Or, you can put the summary in a formula.  It looks like you may have been trying to do that in your POTOTAL formula, but you didn't get the syntax quite right.  You need () around the Sum function arguments.  You had

sum {PO_ITEM.COST}

 and it should be

sum ({PO_ITEM.COST})

 FYI, if you were using groups in the report and wanted a group total, you would add the group field as the second argument:

Sum ({PO_ITEM.COST}, {your group field})


 James
0
 
romeo BallesterosAuthor Commented:
Appreciate all the help.  Woukd not completed without tge assistance.  Thanks.
0
 
James0628Commented:
You're welcome.  Glad I could help.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 10
  • 9
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now