Lookup from a label

[Sparticuz]

I have some information that has been normalized in a customers table I am working on. So, for example, City has the value of 1 instead of Washington. Now, for dropdown boxes Alpha works great, giving me the option to show the city name which is pulled from a city table, instead of the foreign key stored in the customers table.

So now my problem is I want to do the same in a Label instead of a dropdown. The value of the column is 1, and that's what shows up, but there is now way for me to grab the actual city name from another table. (and yes, the label is read-only)

[Sparticuz]

Re: Lookup from a label

btw, i'm using a grid

[Al Buchholz]

Re: Lookup from a label

Change the grid to be based on a view. Link the tables together in the view.

[Sparticuz]

Re: Lookup from a label

ugh.. there's no easier way to do that? (besides using a join).

i saw one of the options was 'custom'. is there anyway to get the information via xbasic? or would that be too slow?

[Al Buchholz]

Re: Lookup from a label

ugh.. there's no easier way to do that? (besides using a join).

Try it before you judge it.

[Sparticuz]

Re: Lookup from a label

the problem is that i have like 10 fk's in this table so the view would be pretty complex

[Al Buchholz]

Re: Lookup from a label

the problem is that i have like 10 fk's in this table so the view would be pretty complex

Why do you need all of the foreign keys defined, when you will only be using one?

[Sparticuz]

Re: Lookup from a label

i am using all of them in my grid, i only said i was using one for simplicity on the forum.

I have a city key that links to a city table with the name along with tax information. A Prospect Type key that links to a prospect table for the different types of prospects, a sales rep key that uses a ulink to link to the employee table. (i use ulink to link back to the security system as well). And a few other keys.

EDIT: this would be my view, but it doesn't work...

SELECT 
	TelemarketingProspects.ProspectID, TelemarketingProspects.ProspectName, 
	TelemarketingProspects.Building_AddressLine1, TelemarketingProspects.Building_AddressLine2, 
	Cities.CityName, TelemarketingProspects.Building_AddressState, 
	TelemarketingProspects.Building_AddressZip, Counties.CountyName, 
	TelemarketingProspects.Contact_Salutation, TelemarketingProspects.Contact_FirstName, 
	TelemarketingProspects.Contact_LastName, TelemarketingProspects.Contact_Phone, 
	TelemarketingProspects.Contact_Extension, TelemarketingProspects.Contact_Fax, 
	TelemarketingProspects.Contact_Email, TelemarketingProspects.Contact_AddressLine1, 
	TelemarketingProspects.Contact_AddressLine2, TelemarketingProspects.Contact_AddressCity, 
	TelemarketingProspects.Contact_AddressState, TelemarketingProspects.Contact_AddressZip, 
	ProspectTypes.ProspectTypeName, SICCodes.SICCodeName, Employee.FirstName, Employee.LastName, 
	EmployeeSizes.EmployeeSizeName, TelemarketingProspects.BuildingSize, 
	TelemarketingProspects.CurrentPrice, TelemarketingProspects.BidDate, 
	TelemarketingProspects.LastCalled, TelemarketingProspects.LastTalked, 
	TelemarketingProspects.NextCall, TelemarketingProspects.NextCallNote, 
	TelemarketingProspects.Proposal_fk, TelemarketingProspects.PrintStatus, 
	TelemarketingProspects.DateEntered, TelemarketingProspects.ModifiedDate 
FROM (Telemarketing.TelemarketingProspects
	 INNER JOIN General.Cities
		 ON  Cities.CityID = TelemarketingProspects.Building_AddressCity_fk 
	 INNER JOIN General.Counties
		 ON  Counties.CountyID = TelemarketingProspects.Building_AddressCounty_fk 
	 INNER JOIN Telemarketing.ProspectTypes
		 ON  ProspectTypes.ProspectTypeID = TelemarketingProspects.ProspectType_fk 
	 INNER JOIN (Telemarketing.SalesReps
		 INNER JOIN General.Employee
			 ON  Employee.ulink = SalesReps.Employee_fk )
		 ON  SalesReps.SalesRepID = TelemarketingProspects.SalesRep_fk 
	 INNER JOIN Telemarketing.SICCodes
		 ON  SICCodes.SICCodeID = TelemarketingProspects.SICCode_fk 
	 INNER JOIN Telemarketing.EmployeeSizes
		 ON  EmployeeSizes.EmployeeSizeID = TelemarketingProspects.EmployeeSize_fk )

[Al Buchholz]

Re: Lookup from a label

EDIT: this would be my view, but it doesn't work...

The view doesn't work in Alpha, or the view doesn't work in the database?

Shouldn't you make a view in the database and then have Alpha use the view?

[Sparticuz]

Re: Lookup from a label

i created the code in alpha using the sql genie, then copied the code to sql management, but it isn't pulling any records...

actually now that i think about it, it may be that the record doesn't have all the keys necessary in the sql alpha wrote...

testing...

yep, that was my problem. i was missing one of the fk's.

now, if i want to update one of the fields in the view, does alpha or sql server take care of using the key instead of the name?

[Al Buchholz]

Re: Lookup from a label

now, if i want to update one of the fields in the view, does alpha or sql server take care of using the key instead of the name?

I don't know. I'm on a strict limit of one question, one answer a day... ;)
Doctor's orders...

[mheller]

Re: Lookup from a label

If Alpha knows the SQL, it will try to update the correct field. If this is an updatable view, then SQL Server is responsible for updating the right field.

The Ajax methods for auto-completes and edit-dropdowns have second argument parameters, so you can pick the related text field and update the FK at the same time.

Note that a label is not updatable. You'll need a text field.