FMP 11: Set Field while in Find Mode
Hi - Please excuse my long post. My background is in English Literature so brevity is difficult for me. I have gleaned a TON of information from these support forums and I appreciate all the knowledgeable people here. I hope one day I can give back to the community a bit.
First though, I need to some more learning. I am pretty green with Filemaker but I am attempting to create a new database for one of our departmental mailing lists.
The mailing lists have a code, such as 100, 101, 102, etc. Each mailing list has an appended number that tells the users when it was used previously, such as -01, -02, or whatnot, so the complete code is 100-01, 100-02 and so on.
However when these are searched, only the initial three digits will be needed, but I want to encompass all the appended numbers.
In order to do this, I have created a temporary holding table to take the user input and append a * to the data. That is, when a user inputs 100, I have that move to a table and result in 100* to search all mailing lists that start with 100.
The problem is when I try to search these appended lists. I cannot Set Field for the mail list code and I think it has to do with this going across tables that are joined by a cartesian join (but then again I might be completely wrong). The join was created to get rid of the <unrelated table> error I received when adding the holding table data to the layout (as this holding data is on a separate table).
My end goal here is to be able to have the user enter several mail codes, then hit "search." What I want Filemaker to do is search on each mail code. My assumption is that I have to do a Find, then extend the found set each time and continue to do a find, extending it until all mail codes have been searched.
This is my script:
#Check to ensure the Search Box has data
If [ IsEmpty ( TemporaryData::SearchCriteria ) ]
Show Custom Dialog [ Title: "Search Box Error"; Message: "Nothing to search"; Buttons: “OK” ]
Exit Script [ ]
#Add a wild card to each search criteria item. Add to "searchmodified" field.
Go to Layout [ “TemporaryData” (TemporaryData) ]
Go to Record/Request/Page [ First ]
Set Field [ TemporaryData::SearchModified; TemporaryData::SearchCriteria & "*" ]
Go to Record/Request/Page [ Next; Exit after last ]
#Go back to original layout, search on criteria
Go to Layout [ original layout ]
Enter Find Mode [ ]
Set Field [ Mailing Lists::MailListCode; TemporaryData::SearchModified ]
Perform Find [ ]
Everything works fine up until the second to the last line here. The field MailListCode does not receive the SearchModified data.
My sense is that Filemaker isn't sure what it is supposed to do with Set Field here because the SearchModified field could contain more than one record. I've messed around with a loop to have it go to SearchModified, search, then extend the set to include the next record in SearchModified, but I am clearly in over my head.
The logic works in my head: Enter find mode, search on the first record in SearchModified. Then get the next record in SearchModified and add the results to the first find. Continue until no records remain in SearchModified. Spit out the results.
Is the join screwing things up? And if so, the unrelated table error returns. I've read some posts that say a cartesian join will get rid of the error (and it did), but other posts say you shouldn't use cartesians unless there is a very special circumstance (which this most likely is not). I have attached a screen shot of this very simple database.
Any feedback would be greatly appreciated. Thank you!