Using a variable in Locate function

[thegame8311]

I'm trying to the locate function with variable

Locate for (Tbl_Name).Person = Variable

I know you can do that with create, select and insert. I don't have indexes on my tables due to the fact of not understanding how to do that.

 

[Olaf Doschke]

The short answer:

LOCATE always and only works on the selected workarea, there is no IN clause, too. The only way is SELECT (Tbl_Name), then Locate For Person = Variable.

Why this is rather clean anyway:

Even in case Person is a field in other tables open in other workareas this will locate in (tbl_name), that's not to worry about.

Why not to use this - unfortunately a longer story:

In very special cases the only seperate threads foxpro generates natively for grids could move the active workarea to the grid.recordsource between running SELECT (Tbl_Name) and Locate. But it's really a very seldom case and only occurring if the grid has focus.

That behaviour has really no reported case in conjunctio with LOCATE in my over ten years of Forum work at all, what HAS bitten some people is doing REPORT FORM on the active workarea in a form with a grid, when NOT reporting from the grid cursor, so it is a real problem in that case and a solution of Christof Wollenhaupt was to move focus to a textbox before doing REPORT FORM.

If you want your Locate code looking like your proposed pseudo code for being clean and verbose, rather use SQL and it's where clause, that's the real deal for querying anyway.

If you just need to locate on the first record of a person for navigational reasons, you could rather index and seek. You say you don't know how, the command to index is called INDEX and is hard to miss. You can also use the table designer to index a table. It's not something you need to put in code every time, it's something you do once only, as you only once do define the structure of a table.

The simplest way: open a table in the table designer, in the list of fields there are columns "Name, Type, Width, Decimal, Index, NULL". Just choose ascending from the Index column and that table field is indexed. Changing the sort order to descending is also always possible via option of SET ORDER later, no need to define an index descending. Look into the index tab of the table designer and see details about the index, you may also change index type from Regular to primary for primary key fields. So actually while creating tables interactively the indexing of them is just two clicks in the Index column of the designer.

Bye, Olaf.

 

[Dan Freeman]

The simpler answer to your question is you need a &macro expansion:

Locate for &Tbl_Name..Person = Variable

Note the double-dot. That's intentional. The first dot is the macro termination character.

 

[Mike Lewis]

TheGame,

You wrote:

Locate for (Tbl_Name).Person = Variable

Are you saying that Tbl_Name is the variable? If so, then Dan's answer is correct.

Or are you saing that "variable" is the variable? If so, then you don't need the parens around Tbl_Name. Apart from that, your code should be fine.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[thegame8311]

Dan's suggestion fixed it

 

[Olaf Doschke]

fine, problem solved.

Do you use relation between some other table and (Tbl_name)?
Because if not, then dans solutions may not error but also not work within the workarea you think it does.

The only reason to specify table/alias names in the fields you search in, is they don't belong to the current workarea.

Bye, Olaf.

 

[thegame8311]

The table name comes from another form passed by a variable

 

[Olaf Doschke]

That doesn't answer if you have set a relation to another table, meaning SET RELATION. If you don't have a relation from the current work area into the alias given by Tbl_name, then the LOCATE does not move the record pointer in that alias.

To show that unwanted effect:

Create Cursor curTable (Person C(10))
Insert into curTable values ("John Smith")
Create Cursor curActive (someotherfield C(20))
Tbl_Name = "curTable"
Variable = "John Smith"
Locate For &Tbl_Name..Person = Variable
? Found()
Select (Tbl_Name)
Locate For Person = Variable
? Found()

This results in .F. for the first locate, while curActive is active and .T. for the second locate, while curTable is active. The first Locate doesn't error, but Locate does search in curActive, not in curTable. Locate always works from the current workarea. To query in other aliases only makes sense, if there is a relation that moves record pointer in them, when the record pointer in the current alias is moved.

Bye, Olaf.

 

[thegame8311]

ok, now i understand, There are 2 tables in this form and I am using locate to make sure that name in the first table in not in the other table, trying to eliminate the possible duplicates when adding to another table. so here there is not SET RELATION, but here is the piece of code that you want to see

ans is a variable that contains a messagebox selection

Team_Name is a variable passed from another form, that has the table that is being added to

lPlyr is a value from a textbox

DO CASE 
	CASE ans = 6
		?TYPE(Team_Name)
		LOCATE FOR &Team_Name..Player = lPlyr
			IF !FOUND()
				INSERT INTO (Team_Name) (Pos, Player, Team, Start_Sit);
					Values (lPos, lPlyr, lTeam, .F.)
			ELSE
				boxtype = 0 + 64

				MESSAGEBOX("Player not added", boxtype, "Selection Cancelled")
				
			ENDIF 
	
	CASE ans = 7
		boxtype = 0 + 64

		MESSAGEBOX("Player not added", boxtype, "Selection Cancelled")
ENDCASE 

[code]

Hopefully this is enough info, if not let me know

 

[Olaf Doschke]

As I showed the locate result depends on which alias is selected. In your case that's not clear, therefore your code might add duplicate players.

If it works for you, the Team_Name name must be active, then you can also do LOCATE for Player = lPlyr

Bye, Olaf.

 

[Olaf Doschke]

Besides the issue with potentilly locatin in the wrong table:

Taking two steps back from a detail to the real world problem, it looks like you want to put a list of players into teams and to avoid one player being put into a team twice, plus a player being put into several teams.

At another thread I already proposed a table design, that would sole that concern: Having a teamid in a player table a player can only be put into one team, and only be put in there once.

It's really just a matter of the right data structures to simplify code and solve problems by making wrong, redundant or contradictory data impossible.

So lets assume you have a list of players, permanent, a list of members of a sports clu, for example. Now you have event, tournaments, matches and you want to put the members into teams for that day. That suggests following tables:

teams
id, number

players
id, name, teamid, further personal data


Now, any player not yet having a teamid is i no team, but any player having a teamid is in that team, and in that team only. See?

And if you need that for several events, and have permanent player data, eg members of a sports club being put into different teams at different events, that suggests the following structure:

players
id, name, further personal data

events
id, name, date

teams
id, number, eventid

teamplayers
id, teamid, playerid

Here a candidate type index on bintoc(teamid)+bintoc(playerid) will care for no double player in a team.

This can be advanced as you like, but in itself it would already solve many of the problems. You seem to group data by copying it into a teable, you seem to create a table per team and this already makes your code more complex than it needs to be, instead of working on several tables you just use the teamplayers filter for a teamid to get a single team, or SELECT ...WHERE teamid = x

This is how tables are meant, they store all equally strucutred data and partition and group it, you don't do the latter with creating tables for each event and each team, that's not good design at all.

Bye, Olaf.

 

[Nifrabar]

Thegame8311 is pretty new in our forum. Having a look at the several threats possibly also new to VFP.
Allthough of topic I therefore would also give an advice regarding naming conventions.
In the code I see e.g. the var ans. For sure this will be an abbreviation of answer. Also hopefully this var has been declared as local. As this also is a numeric here the advised naming should be lnAns. L stands for local while n stands for Numeric.
see

http://fox.wikis.com/wc.dll?Wiki~NamingConventions

Hope this makes sense too.
-Bart

 

[thegame8311]

Hmm that is a interesting strategy Olaf

so the first part of you last answer seems to not exactly fit, because I player can be on multiple teams, but the player can not be on the same team twice

As of the second part, the teamplayers table would be a list of players with all the teams they are on correct?

 

[Olaf Doschke]

Hmm,
if a player can be on muitliple teams, then you can still choose the second table design with

teamplayers
id, teamid, playerid

Also a candidate index on bintoc(teamid)+bintoc(playerid) will care for no double player in a team, but still allow a player to be in there multiple times, each time in a different team.

The desing will depend on other things, too. Of course teams could also be permanent and the same team would take part in several events. That would invalidate the ide to put eventid into the teams table.

Notice, that overall each table can store all data ever needed for all events, there is no table per team or per event. You normally don't overdo in partitioning your data, you rather group by ids, eg eventid. That's the main idea. Eg if clubs play a role in your data, there would be a club table with id,name and
other data related to soem clud would have a clubid, not a table per club. See?

Bye, Olaf.

 

[thegame8311]

ok, I will set that up and hopefully that will make things easier for matching teams and players. If I run into any issues I will let you know

 

[Olaf Doschke]

Cautious: I have a point of view very outside of what you want to achieve, don't take that design for granted, just get the main idea, that is called normalisation of data. Primary and foreign keys.

It's very basic for database design and not specific to vfp or php. If you already did that same app in php I wonder if you don't have some of the same problems and couldn't make your php code base leaner with a database redesign. And as you are more familiar with php that would perhaps be the first thing to do, before you restart in VFP.

Bye, Olaf.