LookUp Help!

[newlyuser]

My client uses VLookUp functionality in Excel Spreadsheet and I am trying to recreate the same calculation in FileMaker but I need some help:

 

I need to calculate Band Number:

 

I have 2 tables; 1st table (client table) have two fields:

Letter Number = eg: 4, C, G

Band Number =

 

2nd table (cost table) have two fields:

Last Digit = eg: 1, 2, 4, C, G

Cost Band = eg: 1, 2, 4, 3, 7

 

I need to get the Band Number from looking at two tables:

 

Here is how the calculation works:

If Letter Number in client table is "4", lookup Last Digit in cost table and "4" in Last Digit is "4" for Cost Band so Band Number is 4

 

How can I do this in FM? Thanks

[Ender]

That's a pretty bad example you've given us to dig through.

 

In general, if you have the relationship established between two tables, you can define a field in the child table (the table on the "many" side of the one-to-many) and give it an auto-enter Lookup or auto-enter calculation to pull in a value from the parent record when the foreign key is populated.

 

If you can provide a clearer description of your structure and give example data that's not the same everywhere, we may be able to give more detailed instructions.