Extracting data from portal

[georgewash]

I am trying to get information from one portal into another portal. Here is my setup:

Products:

Product ID

Projects:

Project ID

ProjProd:

Product ID

Project ID

Proj Item ID

Sales Orders:

Invoice ID

SalesProj:

Project ID

Invoice ID

In the Projects TO I use the ProjProd for a portal, which creates a unique ID for every line item.

In the Sales Orders TO I have a SalesProj portal to extract the items from Projects, both regular fields and the ProjProd portal.

The SalesProj portal has the following fields in this order:

SalesProj:Project ID (drop down menu to select project name)

Projects: Name (pulls correct project name)

Products:Product Name (pulls the products in the project)

SalesProj:Qty

SalesProj:Price

SalesProj:Amt

Everything works except that I can only get 1 item per project to show in the portal.

I thought of using a list, but I don't know how I would perform subtotal, discount, tax, shipping, and total calculations.

Any thoughts or suggestions?

Thanks.

[Fitch]

ProjProd contains the line items for the projects. You want to see the line items for the projects. Therefore, make a portal based on ProjProd.

That's assuming you have a relationship set up like so:

Sales Orders -< SalesProj -< Projects -< ProjProd

[georgewash]

ProjProd is a join table between Products and Projects.

I tried making SalesProj a join between SalesOrders and ProjProd and creating a portal based on SalesProj, but that didn't work.

I am unsure of what to do with your suggestion. It would make sense if the line items in ProjProd were not part of a portal. Then a simple join table would work to get the info into SalesOrders.

I hope this clarifies the issue. I am really in need of help as this is giving me fits in this database and some others I'm working on.

If anyone needs to see the file I'm working on let me know and I'll post it.

[comment]

I am not sure what exactly you expect to see here. You have an order that can have many projects, (incl. the same project several times). Each project instance will be ONE row in the portal to SalesProj (on a layout of Projects).

In turn, each project can have many products - but you cannot place a portal into another portal. You could place another portal on the same layout, this time to Products (shows a list of distinct products) or to ProjProd (shows an aggregate list of products in all ordered projects, incl. duplicates). Or, you could define a calculation field in Projects to list the project's ingredients, and place this field in your existing portal.

[Fitch]

ProjProd is a join table between Products and Projects.

Yes, I left that out of my graph, but to be clear:

Sales Orders -< SalesProj -< Projects -< ProjProd -< Products

I don't think you necessarily need any other relationships. See attached example file.

Let me add that if the purpose of this is to print an invoice, you're better off just doing it from the ProjProd table, using summary fields for totals.

rels.fp7.zip

[georgewash]

Thanks for the feedback so far, I was getting desperate. The file you included (very generous) is not what I am looking for.

Ok, I guess I wasn't clear. I'll try again.

In Projects, I use a portal with related records from ProjProd to select the products that go into the project, as well as the quantity, the price (pulled from another TO), and amt (calculation). Each of these lines in the portal create a serial number called Product Item ID. The reason I added this ID was as a means to get these items into SalesOrders, but so far with no luck. There other portals also in Projects.

In SalesOrders there can only be 1 project selected per invoice. I basically want to take all of the info in the ProjProd portal in Projects and replicate it in SalesOrders. My thought is to select any Project ID from a drop down menu and all of the products, quanities, etc. would be pulled in.

I do not want to create invoices from within Projects. Projects may never become a sale, there are shipping, discount, and tax imlications that require the Sales Order to be seperate.

I suppose I could have a layout for ProjProd and then have that shown in Projets and SalesOrders, but I would prefer to keep as much relevant info on a layout as possible and keep the number down. Besides, I've gotta believe there is a simple way to accomplish what I'm after.

I have included the file, but be warned that this database is based off the BPK and is quite messy. If you look at the three layouts I've been referencing it might help you understand what I'm trying to do.

Thank you very much.

Master.fp7.zip