Multi-relationship lookup

[osiris612mb]

Ok, I can't seem to find an answer anywhere and all my efforts don't seem to work. We have two tables, one is TimeEntries the other being ATRateCodes. I would like for when we enter in the values of RateCode and WA# within the TimeEntry table that it will look up the info related to those fields in the ATRateCodes and brings over the data for Internal and External.

Unfortunately the lookup definition is that when one of those two fields is entered then do the lookup. We need the lookup to be "calculated" by both fields to bring over the correct internal and external fields.

I can upload a sample of this DB if you want to take a look at it... I am just stumped.

Thanks

[Jacob]

I think I may have had a similar problem. What I found to work was to create a separate TO of the table in question and "double relate" it (that is relate two fields in one TO to the corresponding two fields in the other). Might work for you too.

[Himitsu]

Oriris, what you want to do is make a field that has an ID for the ATrateCodes. For everything in ATRateCodes, you should have an ID for each thing right? So, in ATRateCodes table, you should have the two fields that are related, RateCode and WA#. Then in the define table area, go into relationships and connect those two field together. What this means is "show the records that equal the same value of those two fields." Does that make any sense? Remember, relationships are super in that you can make fields equal many things so you can filter out only the info you want. I have a relationship for students in a class. But I want to see only for 2004-2005 school year, Rank level of 10, comes on Monday, only at 3:00 and has Krystine for a teacher. So I have all those fields connected in the relationship table. Now as I enter the data in the field, I can see in a portal the students only for those classes. But if you where only going to use a field, then try making the fields being a autoenter with a lookup first, and uncheck the replace contents if empty and then have it look up the fields that you need to fill out. If that doesn't work to good, then try the autoenter calcultation. If you can, post the files, so we can take a look.

[osiris612mb]

Himitsu

Yes I am 98% sure I understand. i already had a serial number in the ATRateCodes table, and I would select the RateCode and WA# and "drag" them to the TimeEntry table to join with their respective related fileds. And i get

WA# = WA#

And Ratecode = RateCode

Which is fine. But my problem arose when defining the lookup. It says (when a new entry is made in any of the relationship fileds in this table, this lookup will copy........) So that means that if I enter in just the RateCode it will copy over a value? right?

Well I need both fields entered before the lookup is performed. does this make sense?

I will upload a sample DB with some sample data if that might help.

PS I was told this would work at a training seminar I took, and it doesn't yet.

FMFexample1.fp7.zip

[Himitsu]

ok, I did some things to it. I don't know if this is what you are trying to do, but I think it is. Let me know.

FMFexample1.zip

[osiris612mb]

Thanks you Himitsu!

ok I would say most of the functionality is there but alittle confused on a few things. Why have the fields LI_qty and LI_total? and Where is WA_UID used? As well as the lookup for TimeEntry::WA has it showing the WA and rate, but each WA has two rates so it only shows the first one. If I can get a chance today, I am going to make some slight changes and will attach the file to the post when done.

[Himitsu]

osiris, you don't have to have the qty field, I just put it there just in case you charge someone for two of the rates. As for the UID field, it is a calculation that puts the items together. Remember if you use valuelists that lookup from fields, those values have to be unique. You had two of the same WA name and that is ok, but in a value list, it will think they are the same and show only one. This way, the values are unique for one field. As for the rate showing only for the WA name you picked, that is a little harder to understand. For me, it seems backward. But I set a relationship that a value list will use. And we want to show values that have the same value of something.

As for what you are doing in the first one doesn't seem to make sense. You are basically just re creating the same tables. TimeEntry and the other. The fields are almost the same... what you want to think of it as one is a product table, rates you have to sell, and the other is the sales table. YOu should sit down first with a pencil and paper and draw out what you are needing and which table does what. If it is sales, then a basic 3 table system will work great. You can do it with two but in the future, you want options not a headaches. If you had customers too, you might want to make it a 4 table system one being customers, one being products, another being sales, and the last is what we call a line item table. LI (line Item) just stores the info for the sales invoice. You can think of it as parent relationships. Sales table is main and the LI table is below that. You could think above it too, and put the customers table above the sales table. Do you understand the reasoning for that? You could, straight from the customers table, make a sale to that customer you are viewing then go to that sale and add the product. That way, it would add the customer to the invoice automatically then when you go the sales area, and add the items, it will tie it all together.

If you clear up with me what you are trying to accomplish, maybe I could give you a solution rather than a tutorial... ahahaa

Hope to hear from you soon.

[osiris612mb]

Himitsu

The DB i uploaded was an example. Our full one had 3 tables, one with our Attorney rate codes, one is a ClientCode list and the third being the TimeEntry table. The clientcodes are codes the lawyers use for billing and use with phone calls and copies/postage. These codes are from 1-2000 and will be recycled once a matter is closed. This part of our database is working fine. The timeEntry is where the lawyer would enter in the client's code, generating all info needed for our billing software i.e. name, matter, and the Billing software number for each matter...... They would have already entered in their Initials (WA), this is used for status reports and to generate the WA# which is used in a our billing software.

(and the lawyers don't need to know this number) and Rate code would then bring over the internal and external rates.( each lawyer has two basic rates, standard and premium which determines the internal, what they get paid, and the external what we bill the client)

I do see a major flaw with my first design and have changed it so they choose either P or S from a drop down menu with the option of creating an other rate scheme. The reason we would need this info to be replicated in the TimeEntry table is clients come and go, rates change each year, and the client code is recycled once a case is closed. We would purge this table at years end and add it to another DB. This would be like the invoice. We would use this table for other business functions that would require all this info to be stored also.

I hope this spells things out better.

The WA_UID now makes complete sense but can it just be the WA and Rate? without stating "Rate :"?

Thanks so much once again!!!

[Himitsu]

I see. So basically it is like a big calculator. So in that case, you wouldn't need the lineItems table... just take it out and put the same fields in the TimeEntry table. Remember that you need to find the main value. That would be the serial for the WA item. Have that a non look up field and the others, that be a lookup based on the relationship of TimeEnty:WA_serial = WA:serial. Now, this is good if the TimeEntry table has only one item being addes to it... but what if there is a chance that there needs to be more than one? If there is, that is where that lineItem table came in. It will let you have almost infinite items for that timeEntry.

AS for the UID, just go to the field in the define DB and click on field option for that field. YOu will see it saying WA & " " & "Rate Type:" & " " & Rate ... anything between the "" will just write as text. The & is adding a new item. I do this when I have employees who use my system so I don't have to keep telling them what all the numbers mean. And it also gives more Uniqueness to the field which, if you remember, helps with the valuelist popup. Take a look at the value lists and see what I did there. You had the values before manually entered in, which is ok, but what if a new rate came in, then everyone would be screaming at you to add that too... but make the value equal what they are looking for from the table they are looking for, and you will never have to update it.

Keep cracking at it, when you come to a wall, let me know.

[Himitsu]

Sorry, I forgot to tell you about why the WA field only shows one value. It is because I have it set that way. If you go into the valuelist area and see the attorneyAll value list, click in it and go to the field area. there are two parts to it, left and right. The left value had the WA field selected and the right side has a check box above it saying "also display values from second field" that one you can change it to the WA# so now when you use it, it won't say "PMM S" but "PMM 5" the 5 being the PMM WA#. It also says "sort by first or second field" at the bottom. Since the first field is a name and the main values we are using, then first is ok. But if you look at the attorney one, you will see that it is sorted by the second. Because the first is a serial number, and to most humans, means nothing. Does that make sense?

[osiris612mb]

Himitsu

I want to thank you so much for this help! I was able to work on it yesterday and actually get it all to work. I didn't use your layouts just bc our users are very non-technical, and need it as simple and straightforward as possible. But with the info you provided I was able to get a working model, for the most part

If you want you can help me out with another thing, since no one else was able to figure that one out.

Lookups

Once again thanks for all your help!