Finding Correct File In Containers

[bdavid]

Hope I explain this correctly....

I am putting together a real estate application and I have a set of deed templates in containers in a table that I want to choose from based on two conditions:

• 
  - State
  - Deed Template Type (A, B, C, D)
  

I have a dropdown list on one layout where I have fields that I want to merge and a value list with template types to choose from. I am having trouble figuring out how I set up a calculation to choose the proper template type from the table with the containers that hold the templates and return the file name into a new field so I perform the merge.

Is there a function that returns the name of the file in the container that can be used in the following calculation:

Case (Property_State = Deed_Template_State AND Deed_Type_A = Deed_Template_A ;

**Return Deed Template Filename****

{ (Property_State = Deed_Template_State AND Deed_Type_B = Deed_Template_B ;

**Return Deed Template Filename****

......

I would also appreciate any other ideas that could simplify this.

Thanks in advance for your help.

Brian

Edited October 6, 2009 by Guest

[comment]

I am having trouble figuring out how I set up a calculation to choose the proper template type

It should be done by a relationship, not by calculation. Make each template a separate record, with fields for state and type - then match on those fields.

[bdavid]

It should be done by a relationship, not by calculation. Make each template a separate record, with fields for state and type - then match on those fields.

I already have this relationship set up but it is the "matching" that I am struggling with. My table relationship is:

Deed List -> Used Deeds

where the deed list has a record for each state, and four possible deed_types in each state. I need to choose the proper template for the deed type, but can't figure out how to do the matching without a case statement or something similar. I know I am missing something in my approach - sorry for the simple minded questions...

[comment]

the deed list has a record for each state, and four possible deed_types

That's exactly the trouble. Each record should have only one type, with a field saying which type it is. IOW, you should have 4x more records than you do now.

[bdavid]

OK - that would cut down on some logic I need to do on the "match", but I'm still struggling with this approach. Could you provide an example of the "match" logic that you assume I would now use?

Thanks -

[comment]

Make the relationship:

Used Deeds::State = Deeds List::State

AND

Used Deeds::Type = Deeds List::Type

Select the state and type in the Used Deeds record, and the correct template will immediately be available as the first (and only) related record in Deeds List.

---

Note: I hope I got that correctly, because your table names are somewhat confusing me.

[bdavid]

Ah - the light went on this time! I'll give this a shot and report back. Thanks for putting up with a newbie.