Relationship Questions

[Glasstream]

I have a beginner question and have not seen an obvious solution on the forum, so if I missed it somewhere, please point me in the right direction.

I have a 3 fields. First one you choose a subject, the second one populates the information that goes under that subject. Works great, but wanting another field to autopopulate a number based on the selection of the second field after it is chosen. I'm sure it is probably easy, thanks for any advice.

[LaRetta]

Hi Glasstream, welcome to FM Forums!

There are three ways to 'auto-populate' a field: 1) Auto-Enter, 2) Calculation and 3) script. If this third field needs to populate with number but allow User to modify it after that, then use Auto-Enter. If the field auto-populating is dependent upon another table, you might need script trigger. Otherwise (as it sounds might work best here), make the number field a calculation. We would need a bit more information to assist in what type of calculation might be used in any of these conditions but here is an example:

field2 is called Status and this new calculation is called cPoints. If Status is 'Active' make cPoints 25, if Status is 'Pending' make cPoints 50 and if Status is 'Inactive' make cPoints 100. So calculation (result is text) would be:

Case (

Status = "Active" ; 25 ;

Status = "Pending" ; 50 ;

Status = "Inactive" ; 100

)

If a Status isn't Active or Pending and the only other condition it could be then is Inactive, then you can drop the final test and just put a default final value. Calculations evaluate in order and stop when they hit the first true (they short-circuit). So the Case() calculation could be (dropping out the gray):

Case (

Status = "Active" ; 25 ;

Status = "Pending" ; 50 ;[color:gray]

Status = "Inactive" ; 100

)

If you want help with the calculation, just ask. You can read up on Case() and If() in Help to get other ideas.

[Glasstream]

Sorry,I probably didn't put enough information in my first post.

My first field is a dropdown to select a catagory ex. Instruction.

When it is selected it populates everything in the next field on the table that has the catagories with the field on that table ex. FileMaker Tutorials.

I have another field in that table that has numbers like 1.1.A.1.1 that are different for every entry, so the table would look like

Instruction FileMaker Tutorials 1.1.A.1.1

Assessment Schools 1.1.B.1.1

etc..

So wanting the 3rd field to auto populate denpending on what was selected in the second field. Plus be non-editable. I have tried the same settings as the first field but either I am missing something or it has to be done in a different way maybe?

Thanks so much for the reply, hope it not something easy that I am missing :P

[LaRetta]

No problem ... when we don't know the answer we are looking for, it isn't easy to formulate the right questions.

If the third field only has one possible end-result and it should 1) change whenever the value in field 2 changes and 2) be non-editable, then a calculation is the way to go but you might also be describing a 3-tier conditional value list or even a relationship (or self-join) to look up the value or possibly you can simply place the related value directly on your layout at this point.

It would be best to post your file (or preferably a sample file containing only the bare essentials with the required fields, relationships and few records to demonstrate your request). I suspect that your structure isn't properly normalized.

Edited September 26, 2010 by Guest
Added sentence

[Glasstream]

Thanks so much for your time LaRetta! I have uploaded the file.

user is admin

password is blank

Thank You again!

Logs.zip

Edited September 26, 2010 by Guest
Change the database to better show what i need, I think :)

[LaRetta]

I'm currently under a tight crunch but here are my observations:

• It would help if you name your primary keys (your IDs) to include the table name they belong to. Many Developers being their IDs indicating whether parent or foreign, ie, if the table is Customers, the primary key would be: kp_Customers_ID. If the customers id appears in the child table, it would be kf_Customer_ID. Personally I just like CustomerID and when it's not the Customers table, I know it's a foreign key. But calling your IDs all "ID", you won't know which is a foreign key without naming it something else. And that can cause confusion.
• You appear to have redundant data; Tasks has Actions and Actions has Tasks. I could help further if I could tell whether you are working with a one-to-many or requiring a join find but without clean understanding of your keys, I simply can't tell.
• Naming a field 'step number' and also 'actions' is confusion in your Itinerary, which is where you need the assistance. I THINK you need a 3-tier conditional value list. But your description implies that the Action pop-up would only have one value (in which case it should be in Tasks and doesn't need to be a different table at all) and Specific Actions is a duplicate?
• Even your layout for Itinerary isn't clear because you can't allow multiple 'tasks' or 'actons' which is what (I believe you need). This means you need a portal to a join table. Please see Comment's blog (link listed below) and look towards bottom for 'join table' and also 'invoices example'. While there, check out Conditional value lists (3-tier).
• Once you can gain clarity of what you need, please post again and share that information.

If we can eliminate the duplicity in your tables (and determine the relationships), we can walk you through what is needed. And maybe others can step in as well. So just explain the purpose of each table Organizations, Actions and Tasks, something like:

An Organization can have many tasks and one task can have many actions. And, in the Itinerary, I need to select the organization, task and then assign multiple actions. ps ... this would be displayed in aportal similar to LineItems on an Invoice (check Comment's example). This is known as a Join table. Anyway, I'll check in later if I get the chance. ^)

Edited September 27, 2010 by Guest
corrected typo

[LaRetta]

I left off the link to Comment's blog - my apology:

Comment's blog

:crazy2:

[Glasstream]

Thanks, I got to fooling around trying different things and it got a bit, well you know.. hopefully this one is easier to read.

I have been looking around the forum and did a new layout from what I have seen in other posts.

I have the first filed called Task, value dropdown.

A second called item, which is populated by whatever is selected in Task.

Then the 3rd is Action, which I would like to populate with the corresponding number like (1.1.B.1.1) or whatever is in the column of the one selected.

Right now if I select Evaluation & Assessment, and I select one in the item field it shows all the numbers that are listed that have Evaluation & Assesment.

I would like it to auto populate the number that corresponds to what is selected in the field (Item), but not allowing it to be edited.

I am very thankful for your time LaRetta and anyone else that might have some input.

Thank You very much!

Test.zip

Edited September 27, 2010 by Guest

[LaRetta]

Your actions are the 'one side to the many Items' so, since you insert the Item into your Itinerary, you don't also need the Action (see attached modifcation file).

You asked that the Action change every time the Item changes. It now does because the FIELD ITSELF comes from the second table occurrence of Tasks called Task 2 (see graph); which joins Items to Items so that Item's related Action is directly available. I have turned off entry to the Actions field since it is actually a simple 'lookup.' If you need to really plant this Action into your Itinerary table (and I don't see why that would be necessary) then you can make Action a regular field and establish a 'lookup' from the Tasks 2 table occurrence.

I confess that I still don't understand the dynamics of what these tables represent and I feel it could be better structured but I cannot suggest because I can only go by the information provided. I hope this is helpful. :smile2:

TestMOD.zip

[LaRetta]

If you had vs. 11, you wouldn't need the Tasks 2 table occurrence. You could simply put a one-row portal to Tasks on your Itinerary layout and filter the portal as:

Itinerary::Item = Tasks::Item

[Glasstream]

Wow LaRetta, thank you very much! while looking at your solution that works, trying to figure out how it works, it looks so simple. This I need to learn more about, so hitting the books and web again.

Again thanks so much for your time, and your example! You are awesome!