Phillip Prahl

Thank you - that makes sense. However, I was trying to avoid adding another TO. Perhaps my question is better phrased as: Is it possible to access a value list in a calculation field? (Similar to accessing a dictionary in python?)

Hi, I have a calculation that starts by getting an "id_user" value from the table "checkins". Instead of returning this id number, I would like to use it ("id_user") to look up a matching "user_name" in table "users". Is this possible? Right now I solve it by the common practice of creating a popup menu where the first value is the id and the second the name. I then only show the second field. However, I was curious if I could somehow do all of it in the calculation field so I could insert the name via a merge field and not having to use a popup menu. Thanks, Phillip

Your suggested solution works great! Thanks for "let"ting me know

Thank you so much! I was also in the process of trying a workaround where I simply have another field that stores the date as an integer and then do my sql query with this field... Will try both solutions and see what happens Thanks again!

Hi, I have the a calculation field running the following: ExecuteSQL ("SELECT MIN(checkins.date_due) FROM checkins WHERE checkins.id_contact=? AND checkins.isComplete=0"; ""; ""; id) If I tell the field that the calculation result is a number, then I appear to get the correct date back in the following format: YYYYMMDD, otherwise I get nothing or a ?. However using this result in a comparison with say get(CurrentDate) does not work. The strange thing is, that if I set the data formatting in the inspector to be 'general' i get YYYY-MM-DD, so it must know its a date somehow? Any help about how to get fop to recognize the sql result as a fmp date would be greatly appreciated!

Makes sense -Thanks again!

I got a bit smarter after I ran into the anchor buoy concept

Thank you for the quick respone. The reason for the id_user is precisely so that I can make a relationship at a later point. My hesitation to use it was that I end up with a rather large relationship graph (well large for a newbie). Basically I feel that I end up with a new relationship graph per layout. Its not quite that 'bad' but let me explain. (This topic may need to be moved to relationships now...) So, say I have a layout that shows records from the table "companies". This layout has a portal that shows both "employments" and "checkins" in separate portals and these show information from users. To accommodate this I have made relationships that looks as shown in (a). Now I would like to also have the "checkins" portal to show info about "contacts", but fmp will not let me do this unless I create a new dependency instance. Which yields (b). So far so good. However, I have another layout which is identical to the above, except it shows records from "contacts". So, as far as I can tell, I have to build a whole new dependency graph which I have shown in (c). It this a proper filemaker workflow? Is it common to have to rebuild very similar dependency graphs as you create new layouts (that show records from the tables)? Thanks again for all the help.

Newbie question here - so please be kind In a record belonging to the table "company"I have a portal that shows related records from the table "tasks". The table "tasks" has a field called "subject" and this is being shown in the portal correctly. The table "tasks" also has a field called "id_user", but rather that just showing this (which is an integer), I would like to show the first name of the user. This information however is not stored in tasks, but in a table called "users". I have found two solutions (I like the latter better), but was curious to what is the common approach. Solution 1: Create a relationship between the table "tasks" and the table "users". Then simply add a merge field with "users::user_name". Solution 2: Create a value list (relating user_id and user_name) and place a pop-up menu in the portal that uses this. Solution 3: ... The one that the newbie didn't even know was possible? In advance, thank you so much for any and all help!

Hi, A very basic question and hopefully a very easy one to solve (first time user here). I've graphed some numbers across a year (nevermind the wrong sort order). I can't seem to be able to reduce the space being used up in the following marked areas (see attachment). Arrow #1. Outsides area in the charting area surrounding the graph. Arrow #2. Y-Axis title area Arrow #3. Repeated Y-Axis value. I would like to minimize or completely get rid of these areas so that the graph can take up all the space or most of it. Right now about 40% of the width of the area is used for unnecessary blank space and/or repeated info (in case of the repeated y axis). Any help would be greatly appreciated. -Phillip