It would seem that if you setup a calculation field, that extracts the estimate/quote number, you could setup a second relationship to show records related by that number. If the quote number is always 5 digits long (which may be a bad assumption), you could simply take the 5 left characters. Otherwise, it might be safer to use a custom function that returns the numbers, until it reaches a non-digit. Ex:
Function Name: Group
Function Parameters: String
Not IsEmpty ( GetAsNumber ( Left ( String; 1 ) ) );
Left ( String; 1 ) & Group ( Right ( String; Length ( String ) - 1 ) )
Hmm, so I did already have a calculation field that stored the quote id. I created a self join for the QuoteItem table, based on "estimate_parent_id" and called that table "QuoteSummary"
Then my layout is attached to the QuoteSummary table, and I am trying to use my portal to display related records from QuoteItem.
This has gotten me further, but there is still something wrong. It is showing only the first vendor, even though it shows all quote items (for all vendors) below that. And it is still creating more than one record, even though the information is the same on each record.
Any further suggestions? I'm sure I have a relationship messed up somewheres......
I would think that you would want the layout based on the Estimates table, and relate the other tables to it by estimate_parent_id.
Well, relating the other tables to estimate by estimate_parent_id doesn't allow me to relate all the separate parts of the estimate to each quote item, per vendor.
I inserted a pic of the current relationship setup, maybe this will make whatever I have wrong easier to spot.
With this setup, if I create 1 estimate (10020), with 2 extra parts (10020-1, 10020-2)- then create a quote out of those for two different vendors (10020ans, 10020hans)- the layout with the portal ends up with 3 records, each with 6 rows. They are all the same except for the estimate_id- which show (in this order) : 10020, 10020-1, and 10020-2 respectively.
*The layout with the portal is attached to the Estimate table, and the portal is trying to get related records from the QuoteItemSummary table.
Make new instances (copies) of the child tables, and relate those with the Estimates table, use them for the portals.
I'm not sure I completely understand this, sorry.
Your suggestion is to make a copy of QuoteItem and Quote tables, then relate both those directly to the Estimate table via estimate_parent_id, and create the portals using those copied tables?
Also, I'm guessing this would include updating any scripts that populated records in the original tables to also write records to these copied tables every time as well?
If you look below, I added three tables (on the left) ...does this look like what you were expecting?
Looking at your relationships closer, I'm not sure I understand the way you have the tables related. In your first post, you mention that each Estimate can have multiple parts, and each quote corresponds to the Estimate ID & Vendor ID, and then the Quote Items relate to the Quotes. But, in your graphic, you have the Estimates table directly related to the Quote Items table. I was trying to provide a shortcut with my previous advice, relating quote items directly to Estimates, but you did that already.
Such a shortcut would not allow any data entry, as there is not enough information to correctly create records in Quote Items. But, before I make any other suggestions, in which table are you storing the Estimate parts (-1, -2, etc)?
Well, I realized I could have also has an "Estimate Items" table, and maybe I will end up needing one- but it didnt seem necessary for what I was doing.
The flow goes like this:
Create new Estimate (lets say 10020), if there are no other parts to it, you can enter a vendor code and click "Create Quote". That button will create a matching Quote item (10020) for that vendor AND, (if a quote does not already exist for that Quote ID and requested Vendor combo), it will also create a record in quote (10020 + vendor code).
Now, if I add another item to that Estimate- say 10020-1, choose the same vendor and click create quote- it will add a new quote item (10020-1) for that vendor, and since it will find a parent quote (10020) already exists for that vendor in quote table, it will just append that quote item to the quote (quote 10020 + vendor code will now contain both quote item parts on one quote).
This pattern can be repeated for mutiple vendors. It all "seems" to be working ok. The trouble came when I wanted a control panel to show these estimates along with there associated parts, grouped by quotes with the vendors.
The idea is to type in a search for parent Estimate 10020, then the portal should return a list that includes all associated records where a quoted bid price can be entered. the winnind bid then gets a purchase order created which displays info about those quote items along with a few other things:
Estimate 10020 (includes parts 10020-1, 10020-2)
Vendor- JAC Inc.
Quote 10020JAC bid price $75.00 [create PO for this quote]
Quote 10020-1JAC bid price $80.00
Vendor- Hans Inc.
Quote 10020Hans bid price $83.00 [create PO for this quote]
Quote 10020-1Hans bid price $93.00
Quote 10020-2Hans bid price $97.00
*Just to clarify also- the Estimate table has 1 record for every estimate part (a parent is considered 1 estimate part itself). Quote item has 1 record for each estimate part AND vendor code, so there can be mutiple records in that table for the same estimate part. Quote is then unique by parent_estimate_id and vendor code, so there should never be more records in Quote than in Quote items. Hope that makes sense.
Was there any other information I should provide to help me get closer to a solution for this?
Definitely sounds like Estimate needs to be related to Quote. The problem you will have with a single layout used for all data entry for a single quote, is properly identifying how the records you create in a portal relate to the Estimate, Quote, and Vendor. If you want that functionality, you'll need to include fields in the portals to select the proper 'key' values.
I'll try to play around with relating Estimate (I'm asumming you mean directly?) to Quote. But it seems like QuoteItem also needs to be directly related to Estimate to pull in all the specs......Quote was just a combination of QuoteItems.
Since QuoteItems is related to Quote, and in turn, Quote is related to Estimates, you shouldn't have to use a direct relationship from QuoteItems to Estimate. Think of it this way - what is the first thing you create in the process? And the second, third, and fourth? If you start with an estimate, then create a quote, and then specify quote items, that's the order you want your relationships.
Right, I actually create an Estimate, then a Quote Item, then a Quote by combining Quote Items....
So that is my original layout, and leaves me in the same situation as when I started.
I honestly have almost all of this working, maybe I need another table somewhere that can do some kind of in-between relationship for pricing and a report type view.
I'll have to keep digging.
Again, for a view only layout, you need:
A layout based on Estimates
A new relationship, between Estimates & Quote Items, based on estimate parent ID
A portal based on that related Quote Items table
If you make the estimate_parent_id field in Quote Items a number field, and not a calculation, you will have the ability to 'Allow creation of records' in the portal, but you'll need to include the fields for specifying the exact Estimate ID and Vendor in the portal.