Multiple table relationship in portal

[T Howard]

I have a table called "Purchase Requests". It is joined to a table called "Funding" by a field labeled "recordID" which is auto generated serial. The table "Purchase Requests" also is joined to a table called "Vendor History" using a field labeled "Purchase Order No". Both "Vendor History" and the "Funding" table contain a field labeled "Bud Ref". What I need is a portal to show all vendor payments using a match between the "Bud Ref" and "Purchase Order No" fields. Is there a way to do this? Thanks.

[T-Square]

A picture (in this case, of the Relationship Graph) would be worth a couple hundred quatloos.

David

[T Howard]

Here is a PDF of the relationship graph. Keep in mind I am a newbie so it may not be organized as well as it should be.

OHS_Purchase_Request_Final__OHC1_.pdf

[T-Square]

Having looked at the picture of your relationship graph, I think a little more explanation of what you want will be helpful.

I am guessing that you have Funding sources (Funding), and different bills assigned to these Funds (purchase requests). But I don't get what the vendor history table does. The fields suggest that it stores past invoice info, but wouldn't that info be in a purchase order record? Or, to put it another way, what is the difference between a purchase request and vendor history?

Moreover, what is the Bud Ref field in: Funding? Vendor History? How is the data entered? Why not create a link between these two?

What information do you expect to get from this? Would some sort of report (rather than a portal) serve your need?

David

[T Howard]

A purchase request often has multiple funding lines. That is why I set up the funding table separately. Both the funding table and the vendor history table contain the bud ref field. The bud ref field in the funding table is manually entered. The vendor history table which is a portal on the layout I am using contains information I import from an external accounting system and it reflects all invoices that have been paid against a purchase order number. Each purchase request is assigned a specific purchase order number but this number is assigned after the funding information is provided. The end result I need is for the vendor history portal to only reflect all payments against the assigned purchase order number in the purchase request table and the bud ref years contained in the funding table. Currently, I have the purchase request table and the vendor history table linked with the purchase order field and it works well. But I don't see a workaround for joining the vendor history table and the funding table to each other because both tables are already related to the purchase request table using recordID for the funding table and the purchase order no. field for the vendor history table. I hope this makes sense.

[T-Square]

T-

I've read through your post several times over the last couple of days, and I still can't get it. That suggests to me structural issues, since I usually can get what folks are trying to do.

For starters, you say a purchase request can have multiple funding lines, but the structure doesn't reflect that. Assuming that purchase requests::recordID is the key field for that table, your structure allows one funder per purchase request. You'd need a join table to allow multiple funders.

Clarifying the structure might help here, since without a join table, it is not possible to have different bud ref entries from which you would select.

That said, could you script a report that first finds the records you request, and then displays them on screen?

I am sorry I'm being obtuse; I'm trying to be helpful, without much luck I'm afraid...

David