Jump to content

masters to all, less custom for some


rivet
 Share

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

Recommended Posts

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.

rates.png

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5710 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
 Share

×
×
  • Create New...

Important Information

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