Display portal rec based on Calculations

[Lakota]

Hello All:

I have a database with several tables and a few relations that are doing one thing: Asking the user though drop down menus to select a bunch of criteria for our suppliers. Those drop-downs include a carrier, state, region and speciality. Once those have been picked, a 5th field is populated which tells the user how much money they can spend on a supplier for that criteria. This is piped into a portal that displays all the data related. What I would like to know is if I can use a calculation field in the portal to display only those records that are less than or equal to the 5th field:

IE:

If you return a set of data that states you can use a Supplier of Widgets in NY, in the Downstate region, who specialize in purple material, the variable field(5th) would tell you that you can spend 247.95/widget from that supplier for that order.

Can i get the corresponding list in the portal to show only those suppliers who costs less than or equal to 247.95? I would imagine this to be a calculation like Abs(SupplierCosts <= max_Price)

Any thoughts would be great!

Thanks

Lakota

[QuinTech]

Hi Lakota, in version 7 you can do this easily, as you can create relationships based on inequalities (so relate your suppliers table to the main table through [Main].[DesiredPrice]>=[suppliers].[Cost].

For earlier versions, you would want to build a relationship off a combo of the Supplier ID and some calculation that showed that the price is less than the max allowed to spend on a supplier. However, you can't do this because that variable (the max allowed) can only be calculated through a relationship, and you can't "double back" on a relationship (that is, use a field in a relationship that is itself calculated using a relationship).

If you give us some more info about how you would calculate that $247.95 figure, maybe we can figure out a way to do this.

Jerry

[Lakota]

Jerry:

Thanks for the info. I am using FMP7. I cant seem to get this up and running. I have a two tables in one file and an additional table in a second related file.I have the top portion of my layout set with dropdown boxes (4 of them) which allow the user to select state/region/supplier/speciality. This generates a list in a portal below of possible vendors. The list can be anywhere from 0 -300 possible vendors.

What the user is actually getting from the first search routine is the Max Cost number that can be spent on the project, i would like to filter the portal based on the results and the field service_fee >=the max_cost field. If you want to see the layouts, i can show them to you.

Please let me know if you can provide any other info/assistance.

Thanks

lakota

[QuinTech]

Which is the correct scenario:

1. Five fields in the relationship:

Carrier = Carrier

Region = Region

State = State

Supplier = Supplier

MaxPrice >= SupplierCost

And the portal is generated from this relationship.

2. Four fields in the relationship:

Carrier = Carrier

Region = Region

State = State

Supplier = Supplier

And MaxPrice is somehow generated from these.

#1 seems like a more typical database function, so i'll go with that for now. If you're trying to limit the portal by a calculation, you're on the wrong track; this can only be done through a relationship. Your first post leads me to believe that this is what you are doing. Try re-doing the relationship so that it includes all 5 of those fields.

I'd gladly look at your files, but i don't have FM7 available to me.

J

[Lakota]

J:

The first scenario is correct. I can rebuild a small example in fm5 if that would help. I am giving the users the first 4 fields to search on IE: Carrier/State/Supplier/Region. This returns the 5th field (MaxPrice). That number needs to then eliminate all but the vendors that are less than or = to that fifth field. I have something in the relationship that is asking that, but it does not seem to work. I will attach in ver 5 unless i hear otherwise.

Thanks

L

[QuinTech]

Thanks for the offer, but no need to attach fp5 files; you're gonna need fp7 functionality for this one (to use the inequality in the relationship). It might be helpful if you took a screenshot of your table occurrences & relationships.

You say you have "something in the relationship;" is that an inequality join? And when you say "it does not seem to work," what unexpected behavior do you see?

Jerry

[Lakota]

Jerry:

I am attaching a zip file that contains 4 screen shots. They are labeled, but if you have any ?'s please let me know.

Thanks for all your help!

Lakota

relations.zip

[QuinTech]

Thanks for the attachments. Why are there two occurrences of "Service Fee" in the portal?

I think at this point i may have to punt to someone who can view the source in FM7! I can't figure out why this is not working. You seem to have set everything up properly. There's no chance that either Max Cost or Service Fee is a text field, is there?

J

[Lakota]

J:

They are both text fields as far as I know. Change them to Numbers?

Lakota

[QuinTech]

YES YES YES! The inequality will not work as expected if they are text fields.

Since 1 comes before 2 alphabetically, if you compare two text fields, 100 < 20 (because the 1 comes before the 2 just like A comes before :. Change them to number and see if it works.

J

Sorry to yell at you in that first line. Exuberance, not anger. : )

[Lakota]

J:

LOL. Thank you for showing me the ways oh Yoda! I spent a large amount of Money and time on the phone with Filemaker support, and I even sent the files over for them to look at and neither of the 4 people who reviewed this case caught that mistake.

THANK YOU THANK YOU THANK YOU.

It works great!

You are the best.

Lakota

[QuinTech]

My pleasure. In general, if i had a question about FM, i would come here (or other places on the web) before calling FM. Not only is it cheaper, you can expect better results.

J