Enter data here, display in portal there

[Mike Kennedy]

The following issue has me stumped:

I'd like to design a comments feature which has a field for entering the text of the comment on one side of the page, with a portal on the other side of the page which displays all of the comments associated with a record (with no blank row for entry).

Here's how I envision it working - after the user types a comment into the "commentsEntry" field and clicks the submit button, the "commentsEntry" field is cleared and the comment is posted in the portal. The portal is not editable.

I'm thinking I need two tables - one for the entry field and another for the portal (where commentsDescription in table 2 is a lookup value from commentsEntry in table 1).

Ideas?

Thank you!

[Reed]

Check out this thread

Whenever you commit the parent record, the contents of the fields appear in the portal and the field is cleared.... there are also plenty of ways to do this with a script as well, but this one needs no script.

[Mike Kennedy]

Hmm. In your example I'm not sure I understand the significance of fk_invoice_temp. Why it is configured as a calculated value and why is invoice_id = fk_invoice_temp?

Thanks again. I'm obviously new to Filemaker.

[Reed]

There are two auto-enter calculations...

The "real" foreign key (invoice_id) to the invoice is populated when the record is created through the lineitemscreate TO. It is given the value of fk_invoice_temp, so it will show up in the portal.

The fk_invoice_temp field is initially set by the relationship when the related record is created, but the Evaluate() function changes it to 0 right after the invoice_id field is populated, clearing the data entry fields. (since the temp key field being set to 0 means it's no longer related to the invoice record.)

[aldipalo]

If I may put my 2 cents in, from what I am reading you would like to add notes/comments to a record so they are sorted and not editable. If this is the case there is a great tutorial at ISO magazine. It allows the user to add comments, puts a date and time stamp on the comment and sorts it in descending order.

Take a look at this:

http://www.filemakermagazine.com/modules.php?op=modload&name=News&file=article&sid=583&mode=thread&order=0&thold=0

BTW Reed, I really like your example. I can see several uses for it in my current application.

[Mike Kennedy]

Aldipalo - thanks for FileMaker Magazine tip. I am not currenlty a member, but might have to join!

Reed- I think I'm really close, but not quite there. Attached are the db schema and the (simple) layout.

I think the I'm getting tripped with the clearing of the comments entry field. When I enter a new comment, the commentID does not increment forward (it's stuck on commentID=9)

Right now the function in comment_text_temp::commentID_temp is:

Evaluate (

If ( IsEmpty (commentID);commentID_temp;0);

commentID

)

Per your example, comments::commentID is a serially increasing number, while comment_text::commentID=comment_text_temp::commentID.

Ideas?

[Mike Kennedy]

Hi Everyone,

An update; I spent 10+ hrs this weekend trying to adopt Reed's cool solution, to no avail. Today I tried to simplify things by creating a new, simple comments db from scratch (attached).

The problem is that I can enter one comment, but the "entry" field is not cleared when the record is committed. I've tracked this down to the following issue: the temp comment ID (in the attached version uid_temp) is not cleared to zero, as it should be by the Evaluate/IsEmpty function in uid_temp. If I go into the comments table and manually set this value to zero, the comments entry field is cleared and I can enter another comment. But entering another comment simply overwrites the existing comment record.

Before I go completely nuts fretting over this issue, would someone be willing to point out my mistake?

Thank you, thank you.

MK

comments.zip

[LaRetta]

Mike,

Important - Have a read on this .

8.0v1 it works ... 8.0v2 it breaks because of a change in version behavior.

This will work (see attached). But I'm unsure if it's totally safe either ... I'm feeling a bit paranoid right now because of this very issue.

LaRetta

commentsREV.zip

[Mike Kennedy]

LaRetta,

It works! For those of you just tuning in, the solution was to replace the script in uid_temp from:

Evaluate (

If ( IsEmpty (uid); uid_temp ; 0 );

uid

)

to:

Case (uid ; "" )

LaRetta - I tested your CreatorSansScript file from the above link (Mac OS 10.4.5, FM 8.0v2). I too found that when I open the calc definition in the TempParentID, the auto clear function breaks. Bizarre. Interestingly, the function that I was working with and the TempParentID both contain the IsEmpty function.

Can I ask why you're not sure your Case (uid ; "" ) solution is safe? At least opening and closing the calc function doesn't break it! :

Thanks again!

[LaRetta]

I would like to find out why the process breaks. Only then can we make qualified assumptions as to the safety of ANY circular calculation. In truth, if it works for you when created in 8.0v2 it will probably always work (in 8.0v2). But what if it's being Served and a load is put on Server so the calc is evaluated locally instead? Will it still work? I haven't done ANY network testing of this new calc (which BTW was NOT my idea). What if FM AGAIN changes the internal behavior? At least keep a list of these types of auto-enter and after each upgrade, check them.

What if the planned updater for the regression calc issue affects it? I'm pretty sure this calc is safe because it reverses the order. But if this is a calc which fires new records in your invoices, I'd avoid it. If for notes (and it breaks) well then it's not as critical.

I plan to continue my testing. I want to understand it. Because really, until I understand it, I will always wonder about EVERY calculation which affects other fields. The issue MAY NOT BE creation order at all! I just wanted to be sure you were aware of various risks with this process.

LaRetta

[Wim Decorte]

LaRetta - I tested your CreatorSansScript file from the above link (Mac OS 10.4.5, FM 8.0v2). I too found that when I open the calc definition in the TempParentID, the auto clear function breaks. Bizarre. Interestingly, the function that I was working with and the TempParentID both contain the IsEmpty function.

Can I ask why you're not sure your Case (uid ; "" ) solution is safe? At least opening and closing the calc function doesn't break it! :

Your first paragraph answers the question basically. What L found was that while something was working in v1, it broke in v2. So this working v2 solution may break in v3 or 9.0 ...

[Wim Decorte]

To elaborate a little more: FM doesn't have native event handlers. Meaning you can't write code that gets triggered before, during or after an event happens.

Auto-enter calcs are about as close as we can get to the internal events and return results based on them.

But there is virtually no documentation on the order in which various internal FM events happen so you really have no documented (aka supported, safe) way of knowing what will happen when. And as L has discovered, things do change under the hood.

With that in mind I agree with L that to use these techniques can be useful but I'd stay away from them for crucial parts of my solutions and go with an events plugin instead. Not as "cool" maybe, but infinitely more safe and robust.

[comment]

The first file referred to in this thread has circular reference: there are two fields that reference each other. In versions up until 8.02, the order of evaluation depends on the order in which fields were created. If the creation order is reversed, the file no longer works in version 7.

New in FileMaker Pro 8.0v2

...

1.2. Calculations

1.2.1. Fields that have auto-entered calculations depending on other fields no longer rely on field order creation for consistent results.

This change in 8.0v2 makes the creation order irrelevant. But the evaluation order that Filemaker decided is correct, is "wrong" for this file. Therefore it breaks just as if you had reversed the creation order.

The second file, posted by LaRetta, is a bit misleading. Seemingly, it too contains circular reference. But in fact it does not. The foreign key in the child file is populated by record creation, and it will not be evaluated again. The auto-enter calc is entirely redundant.

This is shown in the attached file. It has only one auto-enter calc (to clear the temp field), and (hopefully!!!) works in all versions.

---

Interestingly enough, the attached file was created on September 5, 2005 - making it the oldest file here...

CreatorSansScript.fp7.zip

Edited March 9, 2006 by Guest