Find record in another table based on calculation and selections made on primary table

[Garreth2010]

Hi All,

Please can you assist a Newb, I have recently moved to Filemaker from Access and am having some troubles. I am used to building queries, hence the differences with scripts!!

I have two tables with related data. The primary table (Calcs) has a field who's answer is based on a calculation, there are another 4 columns who records are based on user selections. I need these answers to look up a related record in the secondary table (Pumps). What makes it more difficult the find must take into account the calculation result from the first table is an arbitrary number ex 5.3338 the find must be based on values between 30% less and 50% more as well as the other selected data.

Table one columns: PumpCapacity, Pressure, Voltage, Controls, Other

Table two columns: PumpCapacity, Pressure, Voltage, Controls, Other

I can create a unique key due to the differences in selections and data types.

I have attempted to create a find script, but it only works on the Pump table and can't seem to find how to bring in the Calcs table to the find query.

In short:

Find records in Table Pumps that match (PumpCapacity*0.7 ... PumpCapacity*1.5) AND Pressure<Pumps::Pressure AND Voltage"220Vac 1ph 50Hz" AND Controls"A, B, C" AND Other "A,B,C"

Thank you in advance

[LaRetta]

Hi, welcome to FM Forums!! :^)

I’m afraid that the Calcs table is throwing me. If it holds calculations derived from other tables then the other tables should be related directly. If the purpose of the Calcs table is to hold universal, Management-controlled values then it is known as a Preferences table. If it also contains four duplicate Pumps table fields so Users can enter their find criteria for Pumps then these four Calcs fields should be global so each User maintains their own values. Are they?

Just so we're on same page, please refer to 'fields' instead of 'columns' so we are all talking FM talk. How are the tables related?

What makes it more difficult the find must take into account the calculation result from the first table is an arbitrary number ex 5.3338 the find must be based on values between 30% less and 50% more as well as the other selected data.

This is easy. After the User enters their find criteria, script would take those values and perform the required math before using the result in the find.

I can create a unique key due to the differences in selections and data types.

Can you explain what you mean here? Can you attach your file so we can see what you have?

[IdealData]

I don't believe that this is a scripting issue, it is a RELATIONSHIP issue, and how you have joined the PRIMARY and SECONDARY tables.

I'm with LaRetta on using globals, but you may not know what they are yet.

In FileMaker a layout must be based upon the context of a single table (actually a table occurrence, aka TO) and you can also display a portal of a related TO as a scrollable list. This where you will likely want to display a matching list of pumps, based on the results from your calcs.

The sample files provided with FileMaker could give you some clues as there are plenty of simple Primary-->Secondary relationships and portals.

Incidentally, rows are records.

[comment]

I'll take a shot in the dark (this being a Saturday):

In the Calcs table, define 2 calculation fields:

cMinCapacity =

PumpCapacity * 0.7

 

cMaxCapacity =

PumpCapacity * 1.5

Define the relationship between the tables as:

Calcs::cMinCapacity ≤ Pumps::PumpCapacity

AND

Calcs::cMaxCapacity ≥ Pumps::PumpCapacity

AND

Calcs::Pressure < Pumps::Pressure

AND

Calcs::Voltage = Pumps::Voltage

AND

Calcs::Controls = Pumps::Controls

AND

Calcs::Other = Pumps::Other

The pumps that match the criteria can be shown in a portal based on this relationship. Of course, you could also perform a find in the Pumps table directly.

Note the warnings above about the nature of the Calcs table. It must either use global fields, or have a record for each user. Otherwise there will be a conflict between users.

[Garreth2010]

Calcs.fp7.zip

Hi LaRetta, thank you for answering, I have uploaded my working copy (it is rough at this stage!!)

The Calcs table is where the user inputs his raw data in an automated setup to select a pump. The results of the calculations made on his data would then trigger a script to find a suitable matching pump in the pumps table.

The Calculation results have matching fields in both tables.

Thanks for your efforts here

[Garreth2010]

Hi Comment,

Thank you very much, it works perfectly!!

knew I was close :-)

Thanks Comment, IdealData and LaRetta for your valuable input it is greatly appreciated. Have a great weekend