multiple relationships on one layout...

[Lee Strauss]

ok, I know there is a way to do this, but for the life of me I havent been able to figure it out.

 

I'll lay this out with a stripped down example just for understanding purposes. Lets say I have two databases, A and B.

 

There is a layout in database A that needs to have multiple relationships to database B.

 

Database B consists of 'item number' 'item description' and 'item price'. (this is more or less, just a price list, that logs how much each item is and what the description is)

 

the layout in database A is an order form type layout. where there is multiple entry spots for items. the fields are labeled like this:

 

item_number_1 item_description_1 item_price_1

item_number_2 item_description_2 item_price_2

imem_number_3 item_description_3 item_price_3

 

etc etc, until it fills the bottom of the form (so that they can have up to like 15 or so item entries on this order form.)

 

now, i need to be able to enter the item number in database A and have the respective information (description and price) come up in the fields next to it. (using a LOOKUP function so that description can be modified if needs be).

 

Now, my progress as of right now is that I have 'item_number_1' in database A RELATED (in relationships under 'define database') to 'item_number' in database B. i set the rest of the 'item_description (2, 3, 4, etc) in database A to 'look-up' 'item_description' in database B, thinking it would refer back to the item number to find the information since it is what is related. However when i do this, all of the information in the description fields in database A are the same (all referring to 'item_number_1'

 

another attempt was when I tried to 'relate' item_number_1, item_number_2, and item_number_3, etc (all in database A) all to 'item_number' in database B. when i do this, the lookup function does not even look up anything when a item number is input in a field.

 

ok, does that make any sense? i know it is lengthy and confusing but i did my best to clarify my problem.

 

any help is GREATLY appreciated.

 

thanks!

-lee

[bikergeek]

You need a relation defined for EACH FIELD called item_number_[n] relating that field in table A to tableB::item_number. The description and price lookups for each numbered item_number field will use the relationships defined for that numbered field.

[bikergeek]

Another option:

 

Create a single relationship between tableB::item_number and tableA::calcField

 

Define calcField = Evaluate ( If ( Position ( Get ( ActiveFieldName ) ; "item_number" ; 1 ; 1 ) ≠ 0 ; Get ( ActiveFieldContents ) ; "" ) ;

[ item_number_1 ; item_number_2 ; ... ] )

 

Between the square brackets, list all the 'item_number' fields.

 

Then, define auto-enter calculations for all of both item_description and item_price fields, with the "do not replace existing value" option unchecked, as [replace "_n" with the number of the matching numbered field]:

Evaluate ( tableB::item_description ; item_number_n )

for the description fields or

Evaluate ( tableB::item_price ; item_number_n )

for the price fields.

 

 

Now, you have a single relationship, and when you change the value of any item_number field, the related data is automatically entered as static text into the description and price fields.

[Lee Strauss]

thanks alot for your help...

now what goes in '(ActiveFieldName)' and '(ActiveFieldContents)'?

[bikergeek]

Those are the the parameters of the Get function. FM parses those keywords to find the desired data.

[Lee Strauss]

so...this calc should work if i want multiple lookups for multiple item numbers on one layout? (several lines down?) there are about 200 items, these wouldn't all be in the calculation, rather just the fields for the calculations correct?

[comment]

IMHO, you can save yourself a lot of trouble by following the established model for this situation (a rather typical one, I should add). Instead of two files/tables (what you call a database), use three: ORDERS, ORDER ITEMS and PRICELIST.

 

The ORDER ITEMS file is related to ORDERS by OrderID field. Typically, on the Order layout you'd have a portal to ORDER ITEMS, allowing you to create an unlimited number of items per order.

 

Once you select the ProductID (typically from a value list based on the codes in PRICELIST), the price (and any other product info you wish to include) will be looked up from PRICELIST, using a single relationship ProductID = ProductID.

[bikergeek]

Right. Just the fields on the user interface layour would be included. So, if the user could only select 15 items, the calc would only specify 15 fields.

 

I.e.:

 

Define calcField = Evaluate ( If ( Position ( Get ( ActiveFieldName ) ; "item_number" ; 1 ; 1 ) ≠ 0 ; Get ( ActiveFieldContents ) ; "" ) ;

[ item_number_1 ; item_number_2 ; ... ; item_number_15 ] )

[Lee Strauss]

would anybody be willing to look at a stripped down sample file? I still haven't been able to make any progress on this and It's incredibly frustrating. hah, it sounds pretty simple, but i'm more or less a FM beginner.

 

thanks very much,

Lee