don't know if it would be possible for you, but one solution would be to modify your field in table B.
instead of unstored calculation, create a number field, with auto-entry based on a calculation that replace current value.
This doesn't work as Bs field won't recalculate via the relationship. Or maybe it can and I'm not doing it right?
The approach you describe will fail as unstored or global values cannot be used on the "child" side of a relationship such as you need for your portal. (Filemaker needs an index for that field in order to support the relationship and such fields by definition do not have an index.)
Mystick suggests a reasonable option, but be advised that this field will not automatically update when you modify the value of a field in Table A that is referenced by your auto-enter calculation. (Auto-enter calculations with "replace current value" will automatically update only for fields that are part of the same table as the auto-enter field.)
You'll need a method to automate the update of this field whenever a referenced field in Table A is modified. One simple way to do this is to set up script triggers on such fields wherever they may be edited on your layouts. THese triggers can use set field to update this field in Table B.
There may be a better work around, but I'd need to see an real life description of what you are trying to do with real values, tables and fields rather than abstract examples.
Cool, thanks. I'll experiment some more with that in mind.
This particular layout is being designed for IWP, so script triggers won't work, but I've included a refresh type button already, so I can run whatever script I need that way.
So I'm still having trouble. I'm trying to do this with both fields in the same table, but only one of my records is updating. I'll explain further what I'm trying to do. There might be an easier way.
I have a layout based upon what is essentially an Invoice table. There is a portal showing active catalog items (albums) from Inventory table. Match field here is global calculation calculating to 1 in Invoice table, and active records in Inventory table marked 1 (inactive 0).
Portal rows can be clicked upon to view more detailed item information in lookup fields next to portal.
I want a quick way to filter portal results. Sales rep calls store and wants to find a certain album, or albums by a given band without scrolling through a large portal list. I want them to be able to enter text into a field (below named gFilter Text) and click a "Filter" button to filter portal results.
The calculation I'm currently using to create a match based on this input text is
If(not IsEmpty(gFilter Text);
PatternCount(Album Name;gFilter Text);
Which calculates to 0> for valid records, or all records =1 if no text is present. Match field on the other side is the same global calculation which =1. (I still haven't gotten to writing in what happens if patternCount returns a value 1>)
Hopefully this is a clear enough description. I can create a sample file instead if needed.
Ok, toss out everything I've already suggested. There's a much better way to filter your portal and it doesn't require updating a field in your portal records.
If you are using filemaker 11, setup a filter expression in portal setup:
( PatternCount(PortalTable::Album Name; LayoutTable::gFilter Text) > 0 ) or IsEmpty ( LayoutTable::gFIlter Text )
If you are using an older copy of filemaker, you can get close to this effect by including a couple of special calclulation fields in your portal relationship.
LayoutTable::cFilterKeywords: Substitute ( gFilter Text ; " " ; ¶ )
PortalTable::cAlbumKeywords: Subsitute ( Album Name; " " ; ¶ )
In your relationship, you include this pair of fields with the pairs of fields you've already specified for your relationship.
This approach only matches whole words and will match any one word in gFilter Text to any one word in Album Name so it may not work for you.
PS. Yeah a SQL style "Like" keyword would be pretty handy here if we could only use it...
I'd like to make it so an exact match is not needed. I went ahead and did a loop script that sets the match field values, so that will work for the time being I think.
One remaining issue is that the portal is not refreshing. I included a Refresh Window script step that flushes the join cache. The portal updates instantly if I click somewhere else on the layout. Any ideas?
Thanks for the help.
What version of filemaker are you using? In filemaker 11, this shouldn't be a problem.
In older versions, you may want to script a find based on your filter text and Capture the list of line item ID's storing them in a global text field that is part of the portal relationship. That will give you partial matches as you've indicated you want.
You might try commit record as another way to get the window to refresh.
FM 10, sorry. Thanks again.
With filemaker 10, try this approach:
If you don't have a serial ID field in line items, define one and use replace field contents to give a serial number to existing records--updating auto entry options at the same time.
Define a portal relationship like this: (names will differ on yours)
Invoices::InvoiceID = LineItems::InvoiceID AND
Invoices::gSelectedItems = LineItems::SerialID
Now create a new layout, SerialIDs, that has one and only one field on it: LineItems::SerialID (and base this layout on LineItems)
Define a new global text field, gSelectedItems and place it on another layout (I'll call it "scratch" here)
Go To Layout ["SerialIDs" (LineItems)
Enter Find Mode 
Set field [ LineItems::AlbumName ; "*" & Invoices::gFilter Text & "*"]
Set Error Capture [on]
Copy All Records
Go To Layout ["Scratch" ]
Go To Layout [original layout]
Commit Record // or try refresh window, but I think you need the commit option for web publishing to tell the server to re-send the page