masters to all, less custom for some

[rivet]

I have a table of service rates. There will be two type of records. A global and a custom.

These service rates will be used for creating service orders. When a line item in a service order is created it will lookup up a rate from the table of service rates in relationship to the account the order is for. But not all accounts are charged the same which result in a custom rate in the service rate table which has a foreign key to the account table as well as a foreign key to the global rate record to maintain the description.

So... I would like to have the service order to show all master rates that have not been customized along with the account specific custom rates. The custom rate records will knock out the master rates. records.

I have been trying this with a series of value lists but the performance is questionable not to mention it feels to much like a hack.

I have attached a snap shot showing a list of all rates as well as the two custom. Ultimately the non-custom items would not be in the list.

[coconutt2000]

Are you custom rates stored in the same table with the master rates?

[coconutt2000]

Okay, here's an idea:

The attached file is a demonstration of how the "not equals" expression in a relationship can be used to exclude items from coming through a portal.

test_db.fp7.zip

[rivet]

yes they are both in the same table. All the master files have a 1 in a field called type.

All custom records will have a key refering to the master

I figured I would put them in the same table for easy managment, but maybe there is another way to look at this

[comment]

Your question is not very clear. First you say a rate needs to be looked up. Then you talk about a value list of rates. I don't quite see the connection between the two. I would think you need a value list to select the service, then look up the appropriate rate.

Seems very similar to the question here.

[coconutt2000]

Attached is a file. You have FM8 Advanced, so you'll be able to see the recursive custom function used to build the dynamic exclude list.

I figured the first file I posted would give you the idea that you'd be using an exclude list. This new file has both the method in the first file, and the dynamic exclude list method in different layouts. Layout 1 is the first method, and layout 3 is the new method.

Essentially, the custom function reads the "item type" field using the relationship that matches the custom items. The item type field is the indicator as to which master item the custom item should replace. The custom function, buildexclude() builds a value list of the item types to exclude.

Now, when you create a portal that specifically excludes item ids that are contained in the exclude list, and matches all master items and relevant custom items, you end up with a list of only the master items that don't have custom items plust the custom items.

If you want to control the order that they appear in the portal, set the value "item type" in ascending order that you want them to appear, and then have the portal sort on that field. That way, the custom items will appear in exactly the same place as the master items they replace.

test_dbv2.fp7.zip

Edited April 16, 2006 by Guest

[rivet]

thanks coconutt2000 , this really helps, it is exactly what I was trying to describe.

[coconutt2000]

I hope it works in the context of how you're working in your file.

I started with a theory of how relationship keys work, hypothesized the structure, and then prayed that the technique would work. Usually my ideas don't work out so well. If you have any further problems, post them here. This technique has various uses in a project I'm working on now, and any help I give you is only helping me avoid problems implementing the same technique myself later on.