What to do!!!

[Jukkie]

Hi there,

I have two databases. One is invoice and the other is material database.

When I'am in the invoice database I have the following fields called: width, lenght & price. (Same as in the material database)

When I fill in the width field the lenght field is comparing the width field with the material database and shows all the lenghts that belongs to the width field. So far so good.

Here is what I want.

When I select the lenghts that belongs to the width I want the price that belong to that specific record.

Please ask me if you need more information, because I don't know where to start.

Greetings Jukkie

[Damocles]

It sounds as though you need to create a new field in each database, then make the databases related based on those new fields....

Create a field, UniqueID, in the Materials database. Have the field set to autoenter a calculated value

"If(Length<100, "0","")&If(Length<10, "0","")&Length&If(Width<100, "0","")&If(Width<10, "0","")&Width"

that inserts the measurements (in cm) into the field. [i tried this, and it works.]

For a piece of material that is 100cm by 200cm, UniqueID will be 100200.

For a piece of material that is 100cm by 50 cm, Unique ID will be 100050.

Then create a field in the "Invoice" database, say "UniqueID". You'll have to type in the most common lengths and widths in individual records. Once you create your list, however, you should be able to get the price of any size piece of material. I included a button that will automatically create the "Invoice records" if needed, and will prompt you for the price.

If you define a relationship between the databases by using the uniqueID, (UniqueID::UniqueID) the "materials" database will look to the "Invoice" database and retrieve the price associated with "100200".

I don't have the appropriate .zip software, so I can't send you the files. Send me an email ([email protected]) and I will send you the files.

Paul

[Damocles]

Sorry, my last post was unnecessarily complicated.... Here's the solution I found since then.

This solution only involves one database (I arbitrarily picked "Materials") with four fields:

Width, Length, Price & GlobalWidth

Put the GlobalWidth field somewhere on the page. This is the field where you will type the width you are seeking.

Create a relationship of Materials back to itself (GlobalWidth::Width) so that any records where GlobalWidth=Width are considered related.

Then, create a portal based on that relationship. Add the Width, Length, and Price fields to the portal. (I made it so the records are sorted by length, just for organization.) For whatever number you type into GlobalWidth, all the records that have the same width will show up in the portal.

I created the database, and will send it to you. Please let me know if this solution is what you were looking for.

Paul

[Ugo DI LUCA]

Paul,

Simply set a calculation in the Materials file with a calculation

cKey = Width&"

[Damocles]

Thanks. I'm a little confused about how the "cKey" calculation works, but I've read your solutions before and suspect yours is the more elegant. A quick question:

Does the c_Match = gWidth & gLenght need to have the &" "& in the middle in order to look like (and relate to) the cKey field?

My first post was an attempt to use a relationship between the two databases, but proved to be unweildy. In response, the author of the original question wrote and said:

I want that after you made a choice which width you want you get all the lengths from the related database that belongs to width. After picking a length I need the price that belongs to that specific record. So the price that belongs to the length and width.

It occurred to me that the problem seemed to be a fairly straightforward issue that would only require one database. A portal based on gWidth::Width allowed all the lengths to be seen when the width is entered into the gWidth field. Without laughing too much, what's the advantage of using two databases for this process?

Thanks!

Paul

[Ugo DI LUCA]

Hi Damocles,

[color:"red"]A MultiLine Key, which is a concanation of Keys separated by a "

[Jukkie]

Thanks guys, for stimulating my left brain. -)

I did use the solution below from Paul.

"If(Length<100, "0","")&If(Length<10, "0","")&Length&If(Width<100, "0","")&If(Width<10, "0","")&Width"

Nice to see there are more ways to handle the problem.

Greetings Jukkie

[Damocles]

Thanks... This is an area I have not been exposed to. I'm already envisioning a redesign of some of our office layouts/functions.

Paul