Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Field defined by auto-enter calc based on related table


Inkling
 Share

Recommended Posts

I'm trying to populate a field in an ItemTable record with an auto entry calculation based on the a field from the most recent location record in the related Movement_Event Table. In other words, I would like the field to display the item's current location.

 

This is the auto-enter calc I've been using for the Current Location field in the Item Table:

 

Choose ( (GetAsNumber (Movement_Event::Move_Date)) = Max ( Movement_Event::Move_Date ); Movement_Event::To_Location)

 

In brief this is not working: the Current Location field is not auto-populating with the new location when I create a new movement record for the related object.

 

I feel that I'm approaching this entirely the wrong way, but my brain is not working too well since receiving bad news earlier today and I would deeply appreciate any help or advice.

 

Inkling

Link to comment
Share on other sites

 

Choose (

(GetAsNumber (Movement_Event::Move_Date)) =

Max ( Movement_Event::Move_Date ),

Movement_Event::To_Location

)

 

 

 

Is going to return Max (Movement_Event::Move_Date) if and only if GetAsNumber(Movement_Event::Move_Date) returns ZERO (0). Choose is a zero-based index.

 

It is going to return Movement_Event::To_Location if and only if GetAsNumber(Movement_Event::Move_Date) is ONE (1).

 

If it is neither 0 nor 1, it isn't going to return anything at all.

 

 

Does that match what you had intended for it to do, before we look for problems elsewhere in the system?

 

 

EDIT: Actually I missed that equals sign in there. I don't think it would ever return anything at all. or maybe it would return Movement_Event::To_Location if and only if (GetAsNumber (Movement_Event::Move_Date)) does not equal Max ( Movement_Event::Move_Date ), and would otherwise return nothing at all -- ??

 

Choose ( test ; result0, result1, result2, etc)

 

Result0 means what gets returned if test = 0. Test has to be a string that returns a number and then you list "this is the answer if test is 0", "this is the answer if test is 1", etc.

 

My apologies if the formula as such is doing exactly what you intended and this comes across as condescending. Wasn't intended to be.

Link to comment
Share on other sites

Thank you for responding AHunter3. My brain is really struggling today and your response is much appreciated. I completely missed the fact that the Choose function first tests for 0 result.

 

After much stumbling about with If and Case functions after your response, I suddenly realised that the easiest solution would be to create a one line, one field (location) portal sorted by date in descending order and therefore showing only the latest location!

 

However, your post made me realise I need to re-visit some calc basics or at least slow down when I am in a preoccupied state.

 

Thanks again.

 

Inkling

Link to comment
Share on other sites

Inkling, why are you populating a field when you can simply display it through a relationship? There may be a reason but usually that is considered redundant data and isn't necessary. Can you explain it for us? You say you want to 'display' the current location. But that is different than also inserting the current location INTO a main record.

 

You have not indicated what this relationship is based upon. But consider this: If you sort that *relationship in descending order then you can just place the related Location FIELD directly on your main layout and it will display the last location - no portal necessary and no need to duplicate the data again in your main table.

 

* In relationship graph, double-click relationship line between the table occurrences and, in relationship dialog, sort it below.

 

LaRetta smiley-laughing

Link to comment
Share on other sites

Thank you LaRetta. I didn't know about using the sorting feature in the relationship to control the value which shows in the related field. I've taught myself FileMaker8 over the past year while building a database and I fear I have a number of holes in my understanding of how things work. I greatly appreciate you taking the time to respond.

 

Inkling

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use