You do have a bad data structure, you should have an applicants table, with a single surname field that you can search on, and a separate indicator field indicating if it's applicant one or two.
But, since you've already set it up, you can search in both surname fields using a third field, and a script that searches them.
Say for instance you have a field "searchName", that you setup. The script for a button would be:
Set Variable [ $name ; yourTable::searchName ]
Enter Find Mode [ no pause ]
Set Field [ yourTable::applicant1surname ; $name ]
Set Field [ yourTable::applicant2surname ; $name ]
You can also do those steps manually by entering find mode (cmd/ctrl + F), typing in surname1, hitting "new request" (cmd/ctrl + N), typing in surname2, and then hitting enter. The above is just the automated "script" of that.
Of course if you had just a single field in an applicants table, you could search just one field and it would be much easier.
Thanks Mike Beargie.
I am a complete novice and still learning everyday so hopefully when I create my next one I will have it all down pat. I wont be rushing to get people to use my next one until it is all complexly right.
SO I entered the script however mine looks different to yours..
Mine has Value:: in the "Set Variable" and in "set field" I cant seem to add the ; $name onto mine. I'm sorry i'm such a novice, what am I doing wrong?
Its really hard to come on here and feel so dumb, its very intimidating..
Welcome to the intimidated club! I'm a member too.
You haven't set it up quite like Mike Beargie described.
You set the variable $name but you didn't use it
Set Field [Initial Enquiry::Surname; $name] and then the same for Set Filed [ Initial Enquiry::Partner Surname; $name]
In the Set Variable dialogue choose the fields you want to set as you have already done but beside Calculated result click on specify and for the calculation type in $name
That should do it.
OMG I nearly cried!!
It worked, I got it!! thank you thank you thank you
I promise when I do this new one it will be set up properly at the beginning
Have you looked at the FM training series. It will likely answer a lot of questions you might have and give you many different ideas on how to build your databases.
There was another recent discussion on database design with many useful links as well
Don't forget the many, many excellent FileMaker and Database learning references Beverly listed in recent threads.
There's also a dedicated Database (and FileMaker itself) training series on Lynda.com for various FileMaker versions, including 14.
If you don't get the database design right and spend the requisite amount of time there, everything that follows can be very painful. Think of the construction metaphor: lay the correct foundation FIRST. (Have blueprints, meaning requirements, other documentation, ...., etc., before "diving in").
P.S. Implementation should be the EASY part. If you're trying to figure out things as you go, slow down, step back and do some design or more analysis.
Thank you all, I plan on immersing myself before stepping off next time.
I have read on here that it is an age old problem, what started out very small and basic for just me alone ( so it worked well enough for what I wanted) has grown as others saw it and decided they needed this and then keep adding on...
It seems to have gotten bigger than Ben Hur LOL