Finding Records 1

[elwood00]

I am migrating from Access to Filemaker... so far it has been easy sailing - but now I hit the first roadblock:

My database contains about 1500 records - those are site locations throughout the Southeast - maintenance people, technicians, etc. get the sites assigned by counties. So one guy might have a block of lets say 30 counties - those blocks get reassigned fairly frequently - here are my questions:

1. Is there no easier way to query for all 30 counties than do 30 broadening sets? I am of course thinking along the lines of logical operators in the argument line

2. Once I have created that - for lack of a better term 'query' - is there a way to save it for later use, or easier update, because sometimes just one county changes.

Thanks for the help

 

[elwood00]

OK - while I am at it - here is my second problem:

I have to export data to another program with very limited import capabilities. For that reason, I need to combine fields, e.g. first_name and last_name into one field NAME.

Is that possible?

Thanks

 

[JeanW]

1. Scripting your requests is here the way to go.
Do a manual search and then make your script.
After that is just matter of clicking on a button to launch the script and find your items.

2. The script will do that or you. If you want to 'safe' your found set, there is also a way to save the found records in a separate file/table and, again with a script and button, retrieve those found sets.

3. To 'combine' textfields just make a new field with an appropriate name, calculation, result text, with the syntax: your first field & your second field.
If you want a space between the two (or more) words:
your first field & " " & your second field.

If you want to do it with a script, you can use the Set Field function. Your script could be sonmething along these lines:
Set Field, your new field, your first field & your second field, with again the same thing for a space...

Put a button on a layout, attach the script to it....

HTH

JW

 

[elwood00]

JW:

Thanks for your answer - well, if that is the only way to do it - I guess I'll have to stick with Access or some other SQL solution - I just have too many differnt, ever changing complex queries - by the time I broadened a search 30 times - I can find them with the Find function of word.....

and instead of saving the searches, saving the results to a different table just makes me cringe.... isn't the purpose of a database exactly to avoid that? I have at least 100 of those different queries.... the size of the database would just explode....

Too bad, I really liked what I saw - talking about straight foward.....

Thanks again

 

[JeanW]

Well, I don't see your problem...

I have several applicatioins with 150+ different search/find scripts.
Several nested find requests, no problem.
Even several changes of files with calls to other find requests, still no problems....

Maybe if you can be more specific about you 'queries' we could put you on the right track....

I have several users who had the same 'thinking' problem as you, untill they discovered the scripts....

Give it a try first...

HTH

 

[elwood00]

I'd love to try - OK - I'll just take an example:

The state of GA - 159 counties - 608 sites - 10 (light checks, ground maintenance, HVAC...etc.) types of different services performed for each site - on average 4 contractors for each service (basically one for NE/ one for NW/ one for SW/ one for SE)= 40 contractors - each contractor gets his sites assigned by counties, so depending on the density of sites per county the work between 5-40 counties - but the NW HVAC guy does not have the same counties as the NW light guy.... so basically you have 40 guys that each have their own individual set of counties - and it does not stop there.... the counties get reassigned on a regular basis (it is a bidding process) - OK for example to get the list of the NW light guy I have to say - All sites, with lighting installation in Dade, Walker, Catoosa, Whitfield, Murray, Fannin, Gilmer, Chattooga...etc.(up to 40 counties) ... you get the picture.... I have to do that with 40 guys....

 

[JeanW]

You could take a look at

http://www.filemakermagazine.com

Locate in the General Public section the topic 'Saved Searches'.
It's a free video with technique files.

It's not that I don't want to help you. For that I need much more info. With the few info you posted I do know it is possible in FM.

To give you the solution for your data we need much more info, (such as, If a contractor lives on or near grid line of one or more territories, is that contractor allowed to work in more than one territory.
Or is he limited to NW Territory if that is his base?)

Are you in FM 6 or FM 7, techniques are a little different in both versions. Meanwhile I work something out, based on your info above....

HTH

 

[JeanW]

Well...we put something together...very rough, but it works....

Basis is a map of GA. Each County is a button.
Click on a button and you have a list of the county, which you can filter.
Out of this list you can filter each service window with the contractor.

Reverse could be, make a choice from contractor and see what he has.

This is a very rough approach, but works with a few clicks.
To change contractor etc, is just matter of changing the underlying relationship to move him to the desired place.

Provide an address if you want the screenshots...

HTH

JW

 

[elwood00]

what would be a good way to provide you with an address? the forum specifically requests not to post email addresses in here to keep spammers away - I am new here, so I don't want to violate the TOS - I am sure you can advise me of how to go about it.

I think i need to head to a book store and find a book about filemaker - I can't seem to think like I need to - I am running into one issue after another, where the solution in Access would be right on my fingertips. E.g. In a form I'd like to show the sites that are close to the site shown in the form - i have a calculated field called nearme that adds LAT and LON - so all I would do in Access is create a query that subtracts the nearme field from the nearme field of the active record and sort the results (the smaller the result, the closer the site) ascending - open a 'portal window' and show the first 10 results of the query. Well, I already found out I cannot open a portal that shows the active table in FMP7 - so do I have to make a copy of the entire database to achieve that?

 

[JeanW]

1. See

http://www.tek-tips.com/viewthread.cfm?qid=973856&page=2

for contact...

2.A basic book is never a bad idea when you start with a new program, although the online help in FM 7 is a good way to start. Better is to make some little files to try techniques out and after that include those techniques in your application.

3. As in Access, FM needs first the information to give you back the way you want 'to look' at the requested records.
Portals are based on relationships. You have to make first your relatioinships (and understand how they work) to show the info in a portal. It is doable in FM. I presume the LAT and Lon are the latitude and longtitude values from the sites. These values need to be in a table first.
With a relationship and a calculation you can have the 10 nearest sites in a portal.

HTH