Finding records in table a second table

[Keith McKay]

I have two tables, A & B, with similar fields.  I want to write a script that will search table B for records that match the fields that are entered in table A.  I am not sure how to do this.  Any suggestions?

[comment]

Please provide some more details. Why do you need two similar tables? And why wouldn't a relationship work for [whatever it is you're trying to accomplish here]?

[Lee Smith]

Hk Keith and welcome to the FM Forums,

Please read the Private Message I sent you.

1 hour ago, Keith McKay said:

I have two tables, A & B, with similar field

Please do not use generic names when referring to you file. The actual names of Fields, Table Occurrences, etc. can be helpful to us in deciphering what your need is.

Lee

[Keith McKay]

What I attempting to do is to create an application where items that are for sale are matched up against wanted items.  Instead of having to always do this check manually I thought the computer could do it much more efficiently.  When I enter an item in the For Sale table, the features of the item would be matched against the items in the Wanted table to see if there any matches.  The same process would happen when an item is added to the Wanted table to see if there are any items in the For Sale table.  I didn't know how to implement this in Filemaker and I just assumed that two different tables would be how I should implement it.  I am very open to suggestions.

[comment]

1 hour ago, Keith McKay said:

I just assumed that two different tables would be how I should implement it.

It really depends on what you eventually intend to do with all this information. But it seems that one table, with a field to distinguish between items wanted and items offered, would be easier to maintain.

In any case, I suspect the matching up would be best done by a relationship - subject to the missing details, like how exactly this needs to work. 

[Keith McKay]

If the computer locates a match between a wanted and a for sale item, then I want it to let me know so I can then contact my customer.  Normally, I have to do constant manual checking which is very time consuming.  This way all I have to do is enter the data one time and forget about it until the computer comes up with match.

[comment]

Yes, but what exactly constitutes a "match"? The devil is in the details. Which "features" do you want to compare, and how tightly each one of them needs to match?  For example, you could specify that the price should match within ± X dollars. Or percent. Or you could assign the items to predefined price brackets and match on that. Or ... And that's just the price.

[Keith McKay]

The details that each item can match against will grow over time as will the number and kind of items than can be matched, price being just one of the items.  I want the program to be very dynamic and have the ability to add features for matching over time.  For example, a press has specifications such as tonnage, speed, and stroke length.  I want the ability to add these features on the fly for better matching.  The ability to match within a certain range would be very helpful.  So, if a press is within 95% of the wanted tonnage the match would still notify me.  I want to be able to match against multiple criteria - not just one.

[comment]

8 minutes ago, Keith McKay said:

I want the program to be very dynamic and have the ability to add features for matching over time.

That could be a problem. Filemaker is not a spreadsheet and it's not very "dynamic" when it comes to adding/removing the properties of a record. What fields do you have in your two tables now?

[Keith McKay]

I probably have a 1000 different types of items so the fields will very different.  Does Filemaker have the ability to add a field to a table within a script?

[comment]

1 hour ago, Keith McKay said:

Does Filemaker have the ability to add a field to a table within a script?

That's the right question to ask in the circumstances - and the answer is a resounding no. Filemaker is based on the paradigm of a developer establishing a more or less permanent schema, while users handle only the data.

Now, there are data models to deal with situations like yours - in particular, the Entity–Attribute–Value model. However, that's not something I would recommend for a beginner to try and implement. And that's just to store the data; if you also intend to build a relationship based on user-defined attributes, that's something even an experienced developer would find quite challenging, I think.

 

Edited May 12, 2017 by comment