I've got a simple calculation totalling values from a portal, but it won't update when one of those values is changed from a different layout.

I tried making it a summary, but its totalling values from a different table, so I can't do it.

See attached. For example, using "Form View - People", try changing Alice's pay rate to 95. Then, in "Form View - Projects", the Total Project Pay for Alpha (at the bottom) will not have updated. Is there any way to fix this?

Any help is greatly appreciated.

Job_Accounting.zip

Strange, I try to do what you suggested and I am not seeing the same result... I am using FMP 7 atm to test this, I will test again in v8 shortly to see if I can recreate this problem...

Have you updated your version of Filemaker Pro to 8v3 yet?

After testing this under 8v3 I am still not seeing this error occur when attempting to follow the same steps you provided in your post. I see the totals all updating like they should...

I can see your problem, and it's due to the calcfield being unstored, the approach you use is pretty unusual, because the price per hour ought to be a looked up value, because you won't have that older projects that are completed are tampered with, if a person suddenly get a substantial payrise.

What you do is more spreadsheet'ish behaviour expectations than the transaction model'ish.

When clicking on the smallish triangle with the GTRR attached, make a scripted replace in the linetotals in the _both_ table which then have to be ordinary STORED numberfields.

--sd

Thanks for trying it, Brian. I don't know why it's not working for you.

sd - your point about payrises is a good one: I didn't think of that. I guess I would want payrises to only take effect on future jobs, not to affect past ones. Can this be achieved using lookup values? How do I make this happen? I'm pretty new to FileMaker.

Thanks for your help.

While playing with another sample solution I did finally see the issue happen with data not updating right away - I think it has a bit to do with making sure the record changes are comitted prior to looking to see if data changed... i.e. make sure you hit enter after you make a change to commit the change before changing to another layout or tab view... Otherwise it does act a little flakey. I noticed that in a sample file Comment used a refresh window step to cause an update to the related data...

Now as for using lookup data, you need to define a new field to store the pay rate instead of using a relationship to get it in your calculation. Just make sure when you define the new field, that it uses the autoenter option to lookup data and use the relationship to PEOPLE to grab the pay rate and store it.

This way the data is stored at the time the record is created and does not change after that point. (unless you do a relookup of data - which is usually ill advised in most cases since it can cause you untold frustration after you find that it has also done a relookup on several other lookup fields...)

I wondered if this made sense - it's the same as I suggested, but does it make sense?

Just make sure when you define the new field, that it uses the autoenter option to lookup data and use the relationship to PEOPLE to grab the pay rate and store it.

--sd

Ah yes. Thank you very much, guys. It's working now.

One last, and unrelated, question: in 'Form View - Projects', if I want to list in the portal all the people who worked on the current job on a certain day, how do I do that? If (in Find mode), I type in the name of the job in the 'Name' field and the date into the 'Date Worked' column of the portal, it lists all records, not just the ones with that date.

Take a look here:

http://www.filemaker.com/help/Script-Steps71.html

--sd

I'm sorry, how does that help? I don't really want to enter any more criteria, I just want it to display only records that match the date I entered. I suspect the problem has to do with it being a portal, but I'm not really sure.

Ah it doesn't straight away! But you need to turn to the related records an make the seaches in the child table, it's quite a risk you would take if you made the non matching records in the child table orphans ...in short can't you except with some trickery make the portal omit the irrelevant records from a search.

But issuing a GTRR(SO) makes a found set, an following it with an ordinary search would ignore the previously established found set (the related records) ...hence the use of Constrain Found Set.

If [ IsValid ( ChildRecords::ForeignKey ) ] 

     Set Error Capture [ On ] 

     Go to Related Record [ From table: “ChildRecords”; Using layout: “ChildRecords” (ChildRecords) ] [ Show only related records ] 

     Enter Find Mode [  ] 

     Set Field [ ChildRecords::theDate; Get ( CurrentDate ) ] 

     Show Custom Dialog [ Message: "What date do you wish to single out??"; Buttons: “OK”; Input #1: ChildRecords::theDate, "The Date to search for" ] 

     Constrain Found Set [  ] 

     Set Error Capture [ Off ] 

End If 

This means you make the list in the child record, but if you should need to portalize it exists following method. Other methods exists such as making alterations to the parentkey ...which could go terribly wrong if you connection to server breakes down during this particular "chapter" of the session.

--sd

Sorry, but I am really lost now.

I've attached an updated version of the file. In "Form View - People", under the "Weekly Hours" tab, you'll see what I am trying to do. I want the portal to display all records from the given week, plus the total at the bottom. Would you be able to explain (for a novice) step-by-step what to do? Or, if it is not too much to ask, perhaps demonstrate?

Thanks in advance

Job_Accounting.zip

Edited June 30, 200619 yr by Guest

Yes, this isn't a scripting matter at all, take a look above...so there would be no use for the buttons either.

Then did I notice redundance in similar fields named Expense Cost # these ought to be breaked out in related data as well.

--sd

Edited June 30, 200619 yr by Guest

OK, thanks for that diagram... I've copied it into my file, but it's still not giving me the results I want. Now it seems to be showing the same record several times over in the portal (see attached). What am I missing?

If you did manage to alter the file to make it work, maybe it'd be easier if you uploaded it?

Thanks.

Job_Accounting.zip

If you did manage to alter the file to make it work, maybe it'd be easier if you uploaded it?

Allright here you go!! What you should remember is that the portal should reflect the new relation, and dito goes with the fields ...and especially those tunneled from a yet a realtion away, look at the way I've changed your graph again!

Beyond that have I stuffed an autoenter (updating) feature into you choise of date, to prevent choises not mondays:

Week Start-Mod(5+DayOfWeek ( Week Start );7)

--sd

JobAccmod.zip

Yes! That's exactly what I was after. Thank you so much for you help, Søren!