Please explain how I do a "lookup" in FM12

[philipcaplan]

I have two tables, called "retailers" and "discounts".

 

          The "retailers" table includes fields called "id" and "name"

          The "discounts" table includes fields called "retailer_id" and "retailer_name".

 

                    I want the content of 'retailers:name' to be inserted

                    into the field 'discounts:retailer_name'

                    in every record where the fields 'discounts:retailer_id' and 'retailers:id' are matching.

 

I am sure that in old versions of FMPro there used to be a simple "lookup" function that would be used to achieve this, but it seems to me that a relationship needs to be set up before this can be done in FM12.

 

AND I'M A DUMMY -- BUT I CAN'T FIGURE OUT HOW TO SET UP RELATIONS!!

 

Could some kind person give me a "Dummy's How To" on achieving the result described above.

 

Many thanks.    Philip Caplan

[bcooney]

Why do you feel that you need two tables? A retailer has one discount, yes? It's a field in your Retailer table.

 

That said, the relationship should always be between key fields. Retailers::id = Discounts::id_retailer. To display the retailer name on the discount layout, simply place the related field from Retailers.

 

Lookups still exist (look under Options in Define Database). However, this is not a scenario for a lookup, imho.

 

However, if you had a third table, PurchaseOrder, you may wish to lookup a PO::Discount Rate from the Retailer table when a PO is created (as the discount rate should be captured upon PO creation and not change if the discount rate changes in the future).

[philipcaplan]

Hello Moderator.  You ask "Why do you feel that you need two tables?"

 

Because that is what I have been given!!  

 

These tables are the outputs from two databases, provided to me as two CSV files which I have opened and wish to manipulate in FMPro. (FMPro is not only for the **creation** of databases for business use; it can also make an effective way to manipulate date I think! No?).

 

So, no, "A retailer does not have only one discount"

 

The table "discounts" contains about 5000 records, each of which describes in a field called 'description' the text of a discount, code, voucher, or other special offer. But in this table, the company providing it use only an ID number to identify the 'retailer' of each record, and multiple records can have the same 'retailer-id'.

 

It is a separate table that they provide which contains the 'retailer_id' and the 'retailer_name'.

 

What I need to do is to have a field added to the 'discounts' table which displays the 'retailer_name' as well as their id.

 

Not when new data is added to the tables, as the tables already contain all the data.

[eos]

There is no need to copy anything; if, as you say, the IDs in the Discounts table correspond to the IDs in the Retailer table, then you just need to create a relationship between the two tables matching the two ID fields, and you can display the Retailer name in a Discount context. The name field in the Discounts table isn't necessary, since from a Discount “child” you can always relate to its Retailer “parent” and get the name.

 

Displaying the name is not even necessary if you manage your discounts on a Retailer layout; you can use a portal to list the existing discounts for the current Retailer, or create new ones.

 

btw, Barbara (that's bcooney) is right in that you want to perform a lookup (or use other techniques) to copy over a discount when you apply it to a purchase order, invoice or individual line item, because you want to retain the status at that moment in time; but I think that's not the topic at hand.

[philipcaplan]

I must be explaining myself very badly.      Why are people talking about "copying over a discount" or "managing discounts on a Retailer layout."

 

What I said I needed to do was:

 

(a)  manipulate these two tables which have been provided to me as two CSV files which I have opened in FMPro12

in order to

( "populate a field [in] the 'discounts' table with the 'retailer_name' given that it only has their id"

 

My requirement has nothing to do with invoices or purchase orders or any kind of business solution.

 

My requirement is to be get the Retailers' names into a table which has only an id to identify each Retailer, given that I have another table which contains the Retailer's id and name.

[bruceR]

Why populate?

Just DISPLAY the related data.

[doughemi]

 

What I need to do is to have a field added to the 'discounts' table which displays the 'retailer_name' as well as their id.

 

 

No you don't.  A prime rule of a database is DRY -- "Don't Repeat Yourself". Having the same data in two or more places will ultimately result in one or more of them being wrong.

 

Create a relationship between the two tables with Retailers::ID =  Discount::ID .  Now you can display the retailer name by adding a calculation field field in Discounts

RetailerName = Retailers::name .

[philipcaplan]

Dear doughemi

 

I have tried to follow your instruction to "Create a relationship between the two tables with Retailers::ID = Discount::ID (see following screenshot)

 

I then created a Calculated field, as per the next screenshot:

But, when I view the Table,  the Calculated field does not contain any data.

 

Please tell me where I'm going wrong!!!

[doughemi]

I think (the screnshots come out very tiny) the relationship should be

 

Discount::retailerid = Retailers::retailers_id

[philipcaplan]

That's it!    It now works!!!

 

Me stupid, and very thankful to doughemi.

[bcooney]

My first reply,

"That said, the relationship should always be between key fields. Retailers::id = Discounts::id_retailer. To display the retailer name on the discount layout, simply place the related field from Retailers."

 

 

[comment]

display the retailer name by adding a calculation field field in Discounts

RetailerName = Retailers::name .

 

Actually, the calculation field is quite redundant; there is virtually nothing you can do with it that you couldn't do using the original Name field in the related table (well, maybe a thing or two - but none of them are relevant here).

 

Moreover, the calculation field cannot be indexed, so using it for searching and/or sorting will be slower than using the original field directly.