Jump to content
Salesforce and other SMB Solutions are coming soon. ×

How can I show previous line items ordered by contact using 'Invoice' template?


Recommended Posts

Hi All,


I've just joined this terrific forum.


I've been building an application based on the 'Invoice' template in FM11 Pro Advanced and already found answers to a lot of problems here - so, thanks to everyone who has already contributed!


I cannot, however, find a solution to my last problem - although I have a read a few posts on the topic, I haven't managed to get anything to work.


All I want to do (and it seems so simple!), is to show a summary of items a customer has previously ordered on the customer 'contact' page.


I can get a portal to work easily enough showing invoice date, amount etc. But I can't get it to list the 'line items' on the invoice as these are contained in a portal on the invoice. So, I guess I'm trying to call records from one portal into another portal which, as I've read here, isn't possible.


Is there an easy way to achieve this? Any help gratefully received! Thanks in advance.

Link to comment
Share on other sites

Is there an easy way to achieve this? Any help gratefully received!

Yes, but I guess that depends upon your definition of easy.



So, I guess I'm trying to call records from one portal into another portal which, as I've read here, isn't possible.
I assume (and I hate to make assumptions) that you have the following as a starting point: you are on a customer layout (for view) and you have the invoices from that customer listed in a portal.


Inside the first portal create a button that fits alongside the portal fields - you can also designate one of the fields as a button. Just make sure that if you want to edit data in the portal that you do not use that (those) field(s) as a button, something that is never editable like the ID or creation date field. That button activates a script that places the InvoiceID field in a global field in the Customer table. Now just make a new table occurrence in the Relationship tab between the new global field and the Line Items that make up invoice. The portal showing the Line Items is based upon the new table occurrence.


Here is how the "whole thing" works. You somehow end up on a Customer layout, see an Invoice number and date in the first portal. You click on the invoice number. The script assigned to that field stores the invoice number in a global field (does not need to be on the layout) which then creates a relationship in 2nd portal showing the line items.



The script should contain at least these steps:


Set Variable [$InvoiceID ; Invoice::InvoiceID]

Set Field [Customer::gInvoiceID ; $InvoiceID]

**do other script things here as neccesary**

Link to comment
Share on other sites

Add field to line Items called LU_CustomerID_LineItem define it to lookup the Bill To Customer ID

up entry of a line item , each record in the line items will then have an official owner. (that not just by invoice #)


In Invoices create a calculation field called List_LineItems = Substitute(List(Line Items::Product Name); ¶ ; " ,") //stored as text


Of course to show this in the customer detail page you need to duplicate an occurrence of the Invoices [Call it Invoices_CustID.] table and relate it to the Customers :: Customer ID.


Display this in a portal in the customer detail page, Inside this portal display all the invoice detail including the new filed

Invoices_CustID :: List_LineItems


As you said Easy ... right



If you want to include Quantities plus product name , in Line Items create a calculation called Qty_ProductName = Quantity & " - " & Product Name.


In Invoices change to List_LineItems = Substitute(List(Line Items::Qty_ProductName); ¶ ; " ,")


You will get in the portal in Customer Detail 9 - Oranges, 12 - Bananas in the field.


Obviously this is all for new records. If you have existing records in line Items you will have to run a loop script to buzz through the portals in Invoices to set the Custom ID in the line items LU_CustomerID_LineItem field

Link to comment
Share on other sites

Hooray - success!


I tried the option from Chopper which works a treat. I didn't try the script option from Techplan so I don't know if this works.I'm sure it does though. But, for now, my problems are solved!


Thanks again.

Link to comment
Share on other sites

One more little niggle if anyone can help...


I have got the above working but I've had to duplicate the solution for 'Billing customer' and then again for 'Shipping customer' which seems a bit clumsy. If I don't do this, the solution only shows items previously ordered by a customer as long as they are the 'billing customer'. If they are listed as the 'Shipping customer' the list of previously ordered items doesn't show.


As I say, I have found a work-around by simply duplicating the solution, but I wondered if there is a cleaner way to create a 'combined' Billing and customer ID?

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...

Important Information

Terms of Use