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