Jump to content
Sign in to follow this  
brandie

Assign field based on related field

Recommended Posts

Hello everyone,

I know that there is probably a very simple way to do this, but my knowledge of scripting is very limited, so I'm hoping perhaps someone can help me out.

I'm setting up a database that will contain a list of appraisers in one table and a list of loans in another.

The appraisers will be approved to work in various counties - a one to many relationship. The appraisers will also be assigned to various jobs - again a one to many relationship.

What I'd like to be able to do is enter a job, and have the database basically say:

"Okay, the loan is in Franklin County. The following appraisers can work in Franklin County. The next appraiser due for a job is Mr. Smith."

Basically, there will be three appraisers approved in each county, and I need it to cycle through those three appraisers. I'm thinking a serial number might help, but I'm having trouble figuring out how to implement it all.

I will attach the database I have so far in a following post. I realize that my relationships are not working properly - I just can't figure out how to make it all work.

Can anyone point me in the right direction? Thanks so much.

Appraiser_Clone.fp7.zip

Edited by Guest

Share this post


Link to post
Share on other sites

The appraisers will be approved to work in various counties - a one to many relationship.

Are you sure about this? You say there will be three appraisers approved in each county. If this is to be a one to many relationship, then an appraiser must be assigned to jobs in one county only - but that's not what your file shows.

Share this post


Link to post
Share on other sites

Ah so the appraisers to county must be a many to many relationship?

I can see that now.

Will that help me structure my query?

I guess I'm also not sure how to create a many to many relationship. Do I need to create a repeating field as the ID Number for county?

Edited by Guest

Share this post


Link to post
Share on other sites

Will that help me structure my query?

No, I am afraid it will only make it harder - but you must have the correct data structure in place before you can begin tackling the issue of the next due appraiser.

I guess I'm also not sure how to create a many to many relationship. Do I need to create a repeating field as the ID Number for county?

No, you need a join table between Counties and Appraisers, where each assignment is a record, with AppraiserID and CountyID fields.

Re the next appraiser:

This is not a simple issue at all. Suppose a county has three appraisers, A, B, and C. When a new job comes in, the first thing it needs to know is who was given the previous job in this county. This can be found out by building a self-join relationship to Jobs in the same county, sorted by date, or by JobID, descending.

Now suppose the last job went to B, so C is next in line. However, C is not available, so the job goes to A. But what shall be do with the next job to come in? Our method will suggest B (comes after A, who was given the last job), but it should go to C, who has missed a turn.

So we scratch that and move to another method: we'll suggest the appraiser who up to this time has done the least amount of jobs. That's easy enough: we'll just make each Appraiser record count the number of related jobs, and use that to sort the records.

But here's the snag: while A and B work in this county only, C also takes jobs in another county. That means he has a higher job count than A or B, and he will wait a long time for his next assignment.

Or worse, C has retired, and D has taken his place. Since D has zero jobs in his history, he will be suggested for every job that comes in, until he evens the score.

Share this post


Link to post
Share on other sites

Firstly, I really appreciate your help and insight on this.

The first situation you brought up - where C is next in line but gets skipped - shouldn't be a big problem. I can't imagine much of a situation where C would get skipped anyway, but if he were, we would just cycle back around and get him next time. Although we want to divide the jobs evenly, there is no requirement that everything has to be exactly 33% for each.

Would you mind explaining a little more about how to do the "self-join" relationship? I'm not familiar with it. Also,that join table that you mentioned between County and Appraiser - would the Jobs table (the clients list) serve that purpose? Or would it need to be a separate one?

Edited by Guest

Share this post


Link to post
Share on other sites

that join table that you mentioned between County and Appraiser - would the Jobs table (the clients list) serve that purpose? Or would it need to be a separate one?

It would be a separate table - let's say Authorizations, where each record is an individual authorization of one appraiser to operate in one county. You can see an example of this here:

http://www.fmforums.com/forum/showpost.php?post/246136/

I'd suggest you solve this issue first, then add the Jobs table to the mix, and leave the 'who's next' problem until you have all of this working.

Share this post


Link to post
Share on other sites

I'd do something like this, I think.

Note: this is not something I'd ordinarily suggest to a beginner - be prepared to spend some time with it.

CountyAppraisers.fp7.zip

Share this post


Link to post
Share on other sites

Wow. I'm going to have to digest this one. Thank you so much for that head start!! I have a lot to learn on the reverse engineering here. :P

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.