Display portal rec based on Calculations

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

Recommended Posts

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

Share on other sites

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

Share on other sites

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

Share on other sites

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

Share on other sites

• 2 weeks later...

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

Share on other sites

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

Share on other sites

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.

Lakota

relations.zip

Share on other sites

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

Share on other sites

J:

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

Lakota

Share on other sites

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. : )

Share on other sites

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

Share on other sites

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

Share on other sites

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

Create an account

Register a new account