Jump to content

Multiple table relationship in portal


T Howard

This topic is 6103 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 6103 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.