lookup failing

[jadenguy]

i've got a table of sales people, a table of clients, and a table of sales (jobs). when the sales people enter new jobs, they have view only on their clients, and view only on their sales. when they select the client, the sales person is supposed to automatically be inserted (for when somebody else enters jobs in for the sales people). well, it's failing to lookup the sales rep from the viewable client table. is it because they can't view/modify the job before they select the client? because when they select the client, it does add that to the job, but just fails to lookup the rest of the info. so a job without view permissions can be modified if it's a new job?

[T-Square]

Based on your description, it is tough to know exactly. How exactly have you set up the "automatic" entry of sales person? Is it based on their login? If so, how have you set up the login?

Is it based on a lookup to a global field? Is it based on a lookup to the sales people table? How?

David

[jadenguy]

job table fields of importance:

job number (ID)

client

sales rep

address

terms

client table fields:

client

sales rep

address

terms

i have client = client, and a dropdown box in the job entry form with each client's name. when they add a new job, the client's name is the first thing they select. everything else is set to lookup the value and be unmodifiable. i then have the sales reps' view access to sales rep = get(accountname).

if i'm using an account that doesn't have the view restriction, the job looks up perfectly, and the sales reps can see the jobs no problem.

[Fenton]

If the Sales Rep privileges are record-level access restricted to "View only" based on Get (AccountName), then it seems you should auto-enter "Account Name" into the Sales Rep field. It is the last choice for auto-enter options (where you see Creation Date, etc.). Then the record belongs to them, and they can edit.

Otherwise as soon any other data goes into the record and is committed, they do not actually own it (Sales Rep is blank), so no more data can be written by them (or even seen).

[jadenguy]

can i have it autoenter AND lookup the value? other people will be able to enter jobs for them, is the real issue i suppose. but thanks for clearing up the root of the problem. i always have a problem with commitment.

[bcooney]

I know you're focused on another problem, but you should have the relationship btw jobs and client by clientID, not name. Have your popup menu on the job use a value list that is ClientID and ClientName, showing the second value only.

[jadenguy]

truth be told i'm thinking of scrapping the whole database; a previous employee designed and made it all himself, with little experience in coding and database management. it's surprisingly functional, but i don't think he know about the permissions tab, or calculations, or very much about relationships. i'm currently delving deep into the whole relationships thing, and i plan on having it work that way eventually, but for now i want to get one feature i've been working on working before i start redesigning everything from the ground up. the client list is not editable by the sales staff, and the list is a table, so no worries about name changes and whatnot, but i do understand what you mean. this table actually has ZERO serial number ids. the only indexes are ones we write out in a physical notebook in the office...

hopefully jobs will be indexed, and the constituent parts will be a table that connects via that index, regardless of the job number from the notebook, and eventually the contacts will have indexes for multiple people in companies and all sorts of other great stuff i can do when i beat this monster.

ok i'm convinced that commiting the sales rep is not the issue; i've got a month old backup that seems to be working, but it drives me crazy that the new one doesn't work. i'll have to really comb over the details...

Edited November 29, 2007 by Guest

[jadenguy]

i'm severily lacking in the brains department here!

SOLUTION:

have a field for sales rep on the layout i'm working with. i had one of those <> things so it wasn't editable via script and lookup i guess isn't fast enough without that (i am surprised at how often i have to worry about calculation speed in this crappy computer i'm using here at work...)

[Søren Dyhr]

Well the use of lookups are only required where very specific situations needs it, can you justify the use of them over referenced values here, I fail to find this justification??

In my humble opinion does this seem like a straight forward many to many relation, that screams for a join table ... I could be mistaken here, but...?

--sd

[jadenguy]

i'm not sure what a join table is.

[Søren Dyhr]

I guessed, take a look around for the topic many to many! Perhaps starting here:

http://www.filemakerpros.com/joinprint.zip

--sd

[jadenguy]

well, the job is only imprinted with the name of the sales rep that was assigned to the client at the time of entry, so we know who was the person that actually worked on the file, instead of the current sales rep for the client. it might be advantageous to have both bits of info, so that the new rep could see the client's history...

regardless, i'm really just working out the kinks of the database i've taken over while i work on the databse i'd like to implement with proper structure and stuff.

now regarding a join table, wouldn't i only use this if i had multiple sales reps to a single client and multiple clients to a single rep? or am i misunderstanding join tables? but if it were a many to one, i could keep the id the one as a field in the many?

Edited December 3, 2007 by Guest

[Fenton]

I don't really think that your problem has much to do with a join table, though I could be wrong. It has more to do with that new records are viewable, despite the record-level restrictions you mentioned, until you set a field. After which they are only viewable/editable if the restriction passes its Boolean test.

That is why I suggested putting the Account Name into a field which the restriction checks. Otherwise a sales person is going to have trouble, if you tries to enter any other data. It could be a CreatedBy field, just for that purpose, if you want to keep them separate.

This business of who "created" the record, and who is the "current" sales rep for a client is a little tricky, but you should likely have both fields. It makes no sense to me that a sales person would create a record that he then could not see.

[jadenguy]

yeah, that was actually the final solution, and the creation tracking was also included but i wasn't sure if it was germane to the problem. but yeah, apparently it's not enough to have the sales rep viewable via <> and you have to have an actual field. are <<>> field references or whatever something people use around here or is it disliked like repetitions?

regardless, it may be because i have the SLOWEST computer in the whole company, or it might be a legitimate bug/feature in filemaker, but i'm something unexpected was going on...

[Søren Dyhr]

Couldn't you just upload a file showing your problem, you make me shiver when you mention repeating fields, this has nothing at all to do with the speed of the equipment it's runned at, but something very basic which haven't made sense to you yet - say a reverse of one to many aspect of the relation.

--sd

[jadenguy]

well, it's fixed, but i have no idea how the problem arose. regardless of the fact that i don't know how to upload a file to these forums, the file contains sensitive corporate data and i can't even reproduce the bug in a new file. the page i created has lots of elements and it actually takes seconds to render on this machine. i know timing bugs are uncommon, but i've seen them myself a few times.

the thing with repetitions was sort of a bad joke i guess...

[Søren Dyhr]

the thing with repetitions was sort of a bad joke i guess...

Ah you're trying to bait me into making a fool of myself, I see... nice one!

--sd