Hi. I am trying to get the most basic of lookup and fields working.

I have two tables, one is called Period (which has period_id,
description and amount)
Then one is called Test1 which has (RecordId, period_id and Name) and
period_id is related to the Period table. There is a combo box on this
form which works fine. However, I have a field next to it which is next
to the combo called Amount, so in theory when you select the period, it
should show the amount for the period, but it doest. Is this just not
possible?

I have some sample code but I see I cant post the file with the message
so if you want to see a sample of what i want to do, i can email you
the file on request.

Thank you

David

In article <1162410702.003743.43730 (AT) k70g2000cwa (DOT) googlegroups.com>,
"Super Wasabi" <superwasabidave (AT) gmail (DOT) com> wrote:

All you need is a Relationship set-up as:

Test1 -> Period when Test1:eriod_id = Period:eriod_id

Then you can put the Amount field onto any layout - simply drag the
'Field' placeholder from the side toolbar onto the layout, then in the
window that appears make sure to choose the correct Relationship from
the pop-up menu at the top and then the Amount field.

You will get an amount displayed ... as long as the period_id has data
in it that matches a record in the Period table and the Amount field
actually has data as well.

If period_id has the same data for multiple Period table records and
you're trying to get a total Amount, then you instead need to define a
Claculation field in the Test1 table that sums up all the related
record amounts.
eg.
TotalAmount {Calculation, Unstored, Number Result}
= Sum(Relationship::Amount)

Put this on the layout instead of the Relationship::Amount field.



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Thanks Harry I got it working. OK, the think I was doing wrong was the
Drop-down list I setup was using the Description, so the PeriodId was
not going in. So I changed that so that I show the second field which
is the description. However, another problem arises: The drop-down
drops down the Descriptions fine, but when you choose one and the
dropdown rolls up, the PeriodID is displayed :-( Which is not very user
friendly ... a real flaw on FileMakers side. They should allow the
dropdown to show the second field as displayed in the dropdown (hint
hint to any file maker employees!!!! Feature request!!!)

To get around it, I made the combo smaller and then put the description
next to it, not very pretty looking.
Another way is to put the description field over the dropdown, leaving
just the arrow sticking out, so it looks like its not there, but sadly,
when you drop down, it shows the darn periodID in the dropdown at the
very top!!!

Please tell me there is another way :-)

If there is another way to do this please let me know...


Helpful Harry wrote:

In article <1162413590.380576.276670 (AT) e64g2000cwd (DOT) googlegroups.com>,
"Super Wasabi" <superwasabidave (AT) gmail (DOT) com> wrote:

If I understand correctly, the best way would be to use the
Descriptions in the drop-down menu and the relationships instead of the
Period_ID.

The only problem occurs if the text of any one description is also part
of another description. For example you can NOT have:

Tomato
and Tomato Sauce

because "Tomato" is a part of the second one as well as the first - it
causes problems with the relationship links connecting to both.

If you really need the PeriodID as well, it can be set as an auto-enter
calculation via the relationship.


OR ...

You can create a Calculation field that combines the PeriodID and
Description in the Period table, and then use that field to obtain the
drop-down values for the field in the Test1 table.
ie.
PERIOD TABLE
PeriodID_and_Description {Calculation, unstored}
= PeriodID & " - " & Description

If you need the PeriodID by itself you can extract it from the
drop-down menu field via a calculation in the Test1 Table.
eg.
TEST1 TABLE
PeriodID {Calculation, Unstored}
= Left(PeriodID_and_Description,
Position(PeriodID_and_Description, " - ", 1, 1) - 1)




Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)