Assign field based on related field

[brandie]

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 May 27, 2009 by Guest

[comment]

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.

[brandie]

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 May 27, 2009 by Guest

[comment]

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.

[brandie]

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 May 27, 2009 by Guest

[comment]

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.

[brandie]

Okay I used your example and I think I'm on the right track here. Any new ideas for the auto assignment?

Appraiser2_Copy.fp7.zip

[comment]

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

[brandie]

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