Chain Effect

[Anuviel]

Is it possible to accomplish the following - I tried but were not able

Table1, Fields ID, Name

Table2, Fields ID, Name, Last

Table3, Fields ID, Name, Last, Task

Table1::ID = Table2::ID

Table2::Name = Table3::Name

In Table1 I display a portal from Table2

When I enter ID into Table1 and data into the portal (Name, Last) a record is created in Table2 and all works well. I want to know since Table2 now contains the Name which is a relation to Table3 can a record be automatically created in Table3?

Hope I explained it well enough.,

Thank you.

[LaRetta]

YOu shouldn't have a relationship based upon name. But yes, you can use 'Allow Creation of Related' to automatically create the records for you but ... you must type something into a field in table 2 (which is not the [color:green]table2's key fields) and then also type something into any field in table 3 (which is not the [color:green]table3's key fields). But what you are asking makes absolutely no sense because we don't see the vision of what it means. And why would you have 3 tables which appear to hold the same data anyway?

You are STILL breaking relational rules. You have the name & last in in Tables 2 and 3 when it is totally unnecessary because they can be joined ONLY on the unique ID from table 1 (and table 1's name and last can be displayed in the other tables). Also, attempting to seek help based upon rhetorical - when we have no idea what these tables represent, will simply not work. Each solution is different, depending upon the purpose behind the tables. We don't know yours.

LaRetta

Edited October 16, 2007 by Guest
Corrected green

[Ugo DI LUCA]

Yes, we'd need some more explanations.

However, as a first blind answer, you may have a portal from Table 2 that has a field from Table 3. If relations are set to allow creation of related records, you'd have created a record in Table 3 without moving there.

Now, it is a very limited solution, as this would mean ( from what I understand by you mentionning task ), that one contacts would be linked to only one task at a time.

So it's possible, but what for ?

[LaRetta]

you can use 'Allow Creation of Related' to automatically create the records for you but ... you must type something into a field in table 2 (which is not the table2's key fields) and then also type something into any field in table 3 (which is not the table3's key fields).

I suppose I wasn't clear that both typing table2 and table3 fields would be in table 2 [color:red]portal but yes, this setup would make it impossible for tasks (table3) to be many to table 2.

I just couldn't talk myself into addressing the details in something which gives me the heebee-jeebees. At least without more information. It's sure [color:red]GOOD to see your around, Ugo!! :wink2:

Edited October 16, 2007 by Guest

[Anuviel]

Thank you. I just used the setup I provided here as an example as it was the easiest way for me to explain the matter. I do not intend to use this in my project however I was not able to do it myself as a test of my relation understanding. I was missing the part where I needed to type something into the related field besides the key field as LaRetta said:

you must type something into a field in table 2 (which is not the table2's key fields) and then also type something into any field in table 3 (which is not the table3's key fields).

Sorry for not explaining my intention for this post. Thank you so much for your answers, it helps me a lot.

BTW: Is it possible to create a record in related table by only entering the information in the key field and not in any of the other ones? No purpose behind it just wandering if it can be done - could not find it in the manual / literature I have...

Thank you.,

[LaRetta]

Is it possible to create a record in related table by only entering the information in the key field and not in any of the other ones?

Sure. But not from the parent table by typing into a child's portal. Because, that child won't be related and won't be in the portal without the parentID so how could you type into it?

Still ... I feel you've been left with little to go on. So, to understand what I'm talking about, join parent to child on the parentID. Turn on 'allow creation of related' in the graph on the CHILD (portal) side. Place the child portal on a parent layout. Place ANY field from the child table (but not the ID) in the portal. When you type into the blank row, it will create a child and AUTOMATICALLY insert the parent ID into the child's parentID field for you.

If you want to create a child by inserting the ParentID into the child, you must 1) go to child layout, 2) create a new child record and 3) set the child's ParentID with the ParentID. Script would look something like:

Freeze Widow

Set Variable [ $parentID ; Parent::ParentID ]

Go To Layout [ any layout based upon the child table ]

New Record/Request

Set Field [ Child::ParentID ; $parentID ]

Commit Records/Requests

... do anything else you wish to the new child record

Go to Layout [ original layout ]

Again ... without knowing where you are heading with this, it's kinda difficult to help you get there; but I hope this helps.

UPDATE: You can also create a child (from parent layout) by setting it directly, ie, Set Field [ Child::ParentID ; Parent::ParentID ] ... but this would only create ONE child record per parent. If you try to create a second child, you would overwrite the first.

[comment]

Sure. But not from the parent table by typing into a child's portal. Because, that child won't be related and won't be in the portal without the parentID so how could you type into it?

Actually, you can. But whatever you type into the child's matchfield will be overwritten by the parent's matchfield value. Still, a record WILL be created, and it WILL be related to the current parent.

[LaRetta]

Thanks, Michael. Now that is strange. Because if I put a different ParentID into the childs ParentID field (with Allow Creation on), then they will no longer be related and it should disappear from the portal! So you are saying that, because you are allowed to even type into ANY field in the portal, FM will change the childs ParentID to that parent's ID and MAKE them relate?

Interesting!

[comment]

if I put a different ParentID into the childs ParentID field (with Allow Creation on), then they will no longer be related and it should disappear from the portal!

That is true for an already EXISTING child record. But if you type into the last portal row, the newly created record will be forced to be related, overwriting what you have typed.