AnsweredAssumed Answered

How to determine how many records match a search term?

Question asked by Jax on Sep 1, 2016
Latest reply on Sep 4, 2016 by Jax

Dear FM community,


Which commands could be used in a script to calculate the number of matches for a search term? I have a simple database with 2 tables. I would like to add a 3rd that containing terms to search the first 2 and determine how often a search term appears in both added together.


TableA::FieldA    TableB:FieldB       TableC::Term  ::HitCount    

tomato            melon               tomato        2

pear              tomato              pear          1

apple             orange              ..            ..


So, I need to search FieldA, count how many times I encounter the 1st search term, search FieldB, add both numbers and record the calculation in the HitCount field. I searched online but almost all of the advice was about searching records in the FileMaker GUI. The few scripts related to search were not concerned with the number of hits but rather about displaying or processing the content.


This was one of the few relevant discussion. I tried to adapt the script from there

ValueCount( FilterValues( List( TableA::FieldA); "Term"))

but it failed because FieldA is from an unrelated table. I think of TableC as a level above the data level of A and B; that's why I didn't relate it to the others. Because of the same reason I can't use a simple Count(TableC::Term). Not sure how to get this show on the road?


What would a script counting hits have to look like? Your help would be greatly appreciated.


All the best