Lookup

[Aland]

Hi
I import data from an excel spreadsheet into a database that has lookup fields. I have to run through each record double clicking on the lookup field then return to make the lookup fields work. Is there a way to make the links work automatically when I import the data.
Thank Alan

[Al Buchholz]

Hi
I import data from an excel spreadsheet into a database that has lookup fields. I have to run through each record double clicking on the lookup field then return to make the lookup fields work. Is there a way to make the links work automatically when I import the data.
Thank Alan

So translating your phrase... means a field needs to be filled with data from the table that is looked up?

Import doesn't do that and the lookup field rule is a user driven process, so it only fills when the user interface is used.

You can use an update operation to fill the data into a field by defining a set that links the tables together and then updates the field from the linked table if it's value is blank.

Or is there something else going on?

[Ted Giles]

I was about to ask a similar question, but the server wouldn't respond, so,
What would you like have happen?

[Aland]

Hi Thanks for your help.
Have created the set but cannot see how the update operation works.
On the imported data I have an employee number, (I used to type in all the records) as you said the lookup for their charge rate does not work unless i put the employee number in again.
I want to have the charge rate field updated without having to go through each record again.
Alan

[Tom Cone Jr]

Aland, most of the field rules in the table being populated by the import won't "fire" unless you're entering records through a form based on that table.  I believe there are exceptions to that for autoinc and calc field rules. Give the specifics on the lookup you've defined. Tell us about the lookup source. Tell us about the link key value used to identify the record containing the desired field value.

[Al Buchholz]

Aland, most of the field rules in the table being populated by the import won't "fire" unless you're entering records through a form based on that table.� I believe there are exceptions to that for autoinc and calc field rules. Give the specifics on the lookup you've defined. Tell us about the lookup source. Tell us about the link key value used to identify the record containing the desired field value.

Tom

Aland's 4:22 am (at least on my time zone) post went through an approval process for some reason.

Looks like we need an operation tutorial here.

Do you have one handy? I'm underwater for awhile here..

[Aland]

I get the total cost of a project from a database called projectcost. It has an employee number field which is linked by lookup to a staff database which has the information about the employee including a field giving their charge rate.
I import the employee number and hours worked on a project but as previously stated the lookup for the charge rate does not work unless I overtype the employee number.
I was hoping to automate this so I did not have to overtype the employee number on a large number of records every month.
Thanks
Alan

[Al Buchholz]

I get the total cost of a project from a database called projectcost. It has an employee number field which is linked by lookup to a staff database which has the information about the employee including a field giving their charge rate.
I import the employee number and hours worked on a project but as previously stated the lookup for the charge rate does not work unless I overtype the employee number.
I was hoping to automate this so I did not have to overtype the employee number on a large number of records every month.
Thanks
Alan

Got it.

Sets and operations are very powerful and you should get familiar with them.

Open the control panel - Tables/Sets - right click the set that links projectcost with staff linked by employee number - select Create Operation

select Update records - press the create button

In the field column id the rate field in projectcost
in the Expression column id the rate field in staff

On the toolbar click the Select Records button - Choose Specify record selection criteria - Records selected by a filter expression - enter the appropriate filter to get the records that need updating.

Save the operation with a name.

Run this operation after you import the data.

You can script this. Build a script that runs the import, then runs the update.
I highly suggest that you convert the action script to xbasic and run as an xbasic script or make each operation a function and script those in sequence (preferred).

For now just run the operations individually.

Windows/Alpha tends to run action scripts as multi threaded steps which may cause step 2 to run before step 1 is finished. Very messy, Functions eliminate that issue.

[Aland]

Al
Thanks for this, I was able to follow your instruction and it works.
Thanks again.
AlanD

[Ted Giles]

There's ALWAYS a way.
Been ducking and diving with Alpha for years.
Al is much younger and better looking than me. Maybe. So he is on the ball.

I haven't found many situations where the Alpha DT developers have not covered the eventualities, however sometimes brain overload hits the wall.
It's not always easy, but like Al, a few of us have hit the buffers and asked for help.
In fact I have an issue at the momen I'm arm wrestling and may need help with.

This is one of 2 really good help forums, but it needs a question and a few grey hairs to answer them.
Glad you got it working.

[madtowng]

I used to fight using sql because it was new and I wasn't building anything for masses. Then I learned sql and discovered Alpha goes out of it's way to work with sql.
This ability to use joins and view with the ability to import the results really make sql much more accommodating then you might think, plus your data would then be
in place for when you want to jump into web projects.
Don't let the unknown deter you, getting data from dbf tables into sql tables is an export and import for each dbf table.

-- this sql provides totals group by project / staff member. 
select p.project, sum(p.materials) `materials`, sum(p.hours * s.chargerate) `labor`, sum( sum(p.materials) + sum(p.hours * s.chargerate) ) `subtotal`
from project p
left join staff s on s.seq = p.staffpk
where p.projectid = :projectid
group by p.projectid, s.staffpk

[Tom Cone Jr]

Aland, be careful with update operations. I recommend making a good backup before running a new update operation for the first time. They are brutally efficient and relentless. Can cause much unexpected damage if not used carefully. Very hard to "reverse" the update if it does something you're not expecting. -- tom