elo

Ok, I'm gonna stop at this point b/c with the corrupt files I just cannot be sure that I understand your DB. I'm not sure if each "CalcSheet" can have multiple trips or not, I decided NOT in the way I laid things out, but maybe they can. That said, if you use a more FM7 like structure it will be easier for other to help you AND you should be able to show SUMMARY fields on a suitable layout that subtotal the fields you want across relationships. Sample.fp7.zip

Ok, I've redone this FM7 style with a single file and attach the relationship diagram. CalcSheet in the original solution does NOT record Truck ID's this is a bug IMHO. A better name for Calcsheet would be mCustomersTrips (using my nomenclature) so I've moved the truck ID over to the Trip record since it didn't really make sense on the calcsheet, though it could be there. For trips, I've eliminated the overly complex 2 types of lineitems (mileage, fuel) and replaced with one. Then I'll try to send the calculations and files that avoid 50 relationships (1 per state) to calcsheet.

Just saw this today (8/22), anyhow, some of the files are corrupt (Line Items.fp7 among them). I cannot make sense of this entirely, but am trying to create something simplified in FM Pro 9 to send back to you.

You don't make what tables you have clear, but I'll see if I can help. You need at least three tables. 1) Recipes table 2) Ingredients table AND 3) RecipesIngredients table to support a many-to-many join. Recipes will have the fields: recipe_id, RecipeName Ingredients will have the fields: ingredient_id, IngredientName RecipesIngrdients will have the fields: recipeingredient_id (optional field, but recommended), recipe_id, ingredient_id The tables will need to be connected up by relationships in the data diagram with the proper _id fields connected. Now, to add an ingredient to a recipe, you add a record to the RecipesIngredient table with the desired ingredient_id and the current recipe_id.

Not to my knowledge.

Assuming I understand this correctly, you have a table like Students and another table StudentModulesTaken. When a student is created, you want to create 18 StudentModulesTaken records? I cannot think of anything short of a button that invokes "Perform Script" on a custom script to do this. The basic flow of the script would be to get the "primary key" or ID field for the StudentTable, then go to a layout for the StudentModulesTaken table, and start creating 18 records with the necessary items. There may be some fancy shortcuts, but at the end of the day, that's what you are going to be doing.

Micah still not sure what you are asking. Just to give a non-ESS example, if you have a table tBigTable that is part of lots of table occurrences (TOs) in the data diagram, even if you just create tNewSmallerTable right in FMPro there isn't going to be a tremendously easy way to "swap" everything pointing to tBigTable to tNewSmallerTable easily and "giving it the same name" doesn't help. So, any way you dice it, you are going to have some FM work to clean things up when you start normalizing your data.

I'm not 100% sure I understand your database, but the simpler way to track this which would also make "rollups" easier is more many-to-many relationships using an intermediate table. Here's what I would do for tables and part of the relationship diagram: tCustomers -> tTrucks -> tTrips -> tMileageItem -> tFuelItem Somewhere you need a lookup table, lStates, each tMileageItem and each tFuelItem would have a stateID from lStates. You should then be able to use summary fields with breaks at changes in stateID for rolling up the summaries. But again, not 100% sure what your DB structure or the problem you are trying to solve.

Can you post what script step options you are using? If you did a Set Variable[$outputfile; < Then your save as PDF should be able to accept $outputfile the destination.

Not 100% sure what you are trying to achieve, but I suspect there is not going to be an easy automagic way to swap the table occurrence (TOs) of all relationships from CurrentFMTable to ESSMYSQLTableNamedCurrentFMTable...

You'll probably get more help if you post the relationships in your db, but long story short. In FMPro, you are going to need to create enough table occurrences to "walk" from (connect) SALESLINES to PRODUCTS. Then all you need to do is generate a layout of quantities for each product during a sales period. As for reconciling the inventory, you probably need some more tables to store the weekly inventory results and then compare the two values.

So, it isn't 100% clear what you are doing, but I am guessing you do not have a key field. The "right" way to set this up is for Accounts to have an "id" field which is unique/auto-enter serial number. File needs to have an "account_id" field. You need to change that account_id field. My guess is that you are instead changing the accounts::firstname field. Erik

Ok, the first issue I was seeing was a transcription error by me. Many thanks to LaRetta for suggesting contacting Nightwing/Ray directly. The others are possible enhancements/corner cases that you may hit/desire if implementing the technique. Again, great book and awesome author for responding.

I've seen this behavior too, I just ended up adding a script step to maximize my original window after closing the utility window.

So, been enjoying the "FileMaker Pro 9 Bible" and decided to implement the saving of the find requests for one of my central layouts. In the process I identified a few glitches in the basic code provided. The first issue was that if your special save--and-do-find script was triggered when no field was active, the script would loop endlessly. The second issue was that the ActiveFieldTableName was not saved this means that if you had two fields (in different TOs) on a find layout then only the first by tab order is "accessible" to the saved/played back find. A third issue was that on "failed" (no record) searches, the script simply went back to browse mode rather than letting you fix your query. This could be rather annoying for users. A minor enhancement was contemplating that the Find and Browse layouts might be different, I made some small modifications so that if the layout was named MyLayoutFind then when browsing the layout MyLayout would be used instead of MyLayoutFind. I can share the revised record and playback scripts if people are interested. It's a pretty nifty technique.

Andy: This is pretty similar in nature to the question I answered for you before... same basic idea would go on here just create more table occurrences (TOs). You might look at the "mock" dialog box technique using an extra window that I described from the "FileMaker Pro 9 Bible" http://www.fmforums.com/forum/showtopic.php?tid/196901

Andy: Glad to hear I was able to help and that it worked. It took me being beaten over the head with the issue to realize that the relationship graph isn't what it first appears. I spent most of Friday re-doing my solution using the "anchor-buoy" technique so that I could better be able to manage my database going forward.

This is a common need and I'll try my best to explain what to do assuming you are looking at the Invoice Lines in a portal on an INVOICES layout. Basically, the first step is to throw out traditional relational database thinking (e.g. that the Relationships diagram is some sort of ERD) and (for your own sanity) create some extra table occurences (TO's) in the Manage Database view "Relationships" tab. (1) Call one, say "ProductsHelper" and make it point to your INVOICES table. (2) Create another TO and point it to PRODUCTS, call it "SuppliersProducts" (3) Create a relationship by connecting "ProductsHelper and "SuppliersProducts" by the relevant supplier ID key field This is the basis point, you now have a way of easily finding all of the PRODUCTS permissible for a given invoice by looking at the "SuppliersProduct" TO through the "ProductsHelper" TO The next step is to wire up your Value List to make use of these new TOs: (4) Manage Value Lists (4a) Use value from field [specify field...] button (4b) Use your new "SuppliersProducts" TO in both columns for the first/second fields (4c) use the "include only related values starting from:" set to "ProductsHelper" option Depending on how your layouts are set up this should get you what you want.

Ok, I will do my best to explain it. I think it is a variation of what you proposed. Basic idea: use a utility table with a relationship to a table occurrence (TO) of your preferred valuelist source use a script and some fields turned into buttons to help “launch” you back and forth to a new window showing a layout of the utility table. More details: (1) Let’s assume you have a tMatters table and a tPeople table that are related by mMattersPeople. I’ll use “id” as the primary key for each (mMattersPeople has matter_id and person_id keys). I had been trying to create a value list of tPeople values as a drop down, but ran into the too-long to show/no-typing. (2) New solution (simplified, I’m leaving out the filtering capabilities that “Bible” offers on the utility layout; but I also supplemented it to work on Many-to-Many relationships), add a table gUtility and relate it to the records in tPeople. So now we have a layout “People Picker (gUtility)” which has a portal showing tPeople items. (3) On the relevant tMatters layout, say “Matters (tMatters)” add a button in your portal showing your mMattersPeople items call it say “Pick” and have it invoke a script “PeoplePicker” and pass any parameter in. (4) Make sure you also name the Portal (object viewer) on the Matters layout say “PeoplePortal” (5) Back on the “People Picker (gUtility)” layout, apply a button action to the field inside the tPeople portal so that clicking on the tPeople::Name field invokes “PeoplePicker” without any parameters. Set it to also “Exit” any running scripts. (You also need to change the field behavior to not allow entry into the tPeople::Name field) (6) Wire it all together by writing the script, if the script gets a parameter then it displays the picker/no parameter it goes back and adds the chosen person: If [ not IsEmpty(Get(ScriptParameter))] # called to pick ojects Set Variable [$$portalrow; Value:Get(PortalRowNumber)] New Window [Name: “Select Person”; Height: 400; Width: 400; Top: 10; Left: 10] Show/Hide Status Area [Lock; Hide] Go to Layout[“People Picker” (gUtility)] Enter Browse Mode [] Else # called because used clicked on a person’s name Set Variable [$select; Value: TO_for_tPeople_on_gUtility::id] # e.g. whatever the portal’s TO name is on the “People Picker” layout Close Window[Current Window] # you are now back at your original window showing the Matters layout Free Window Go to Object [Object Name:”PeoplePortal”] Go to Portal Row [No dialog; $$portalrow] Set Field[TO_for_mMattersPeople_on_Matters::inventor_id; $select] # this assumes the TO of mMattersPeople is set to auto create records and all you need is to # plug in the inventor_id Set Variable [$$portalrow; Value: “”] End If Hopefully that’s sufficiently clear. The full solution in the “Bible” uses more global fields on gUtility to offer filtering using more complex relationships with tPeople that might help you narrow down the list (type first few letters of last name; or limit to only certain type of people (employees, vendors, etc.))

Thanks again for the reply, I ended up using the technique outlined in Ray Cologon's "FileMaker Pro 9 Bible" chapter 9 (pp 267-272) Great book!

Thanks, sounds like there is no good built-in way so it is going to involve layering some extra layouts/layout work to do nicely.

Context, I'm working on a system that has lots of People (table tPeople) and items (table tDocket), I relate the two using a third table (table mDocketsPeople). Question/best practice request: What's the best way to help users select people to associate with a matter (or vice-versa). Assume both lists will over time have on the order of several hundred items. What I've tried (talking from the perspective of a layout in tDockets having a portal into mDocketsPeople): Pop-up menu: Works, but doesn't allow typing and easily gets out of hand past say ~50 items Drop-down list: Displays the person_id instead of the nice human understandable value name. This is apparently a known issue from searching these forums. Display situation is intolerable for basic usage. So what do people do? I've looked at Nightwing solutions for lists where you do have two scrolling portals and a "move" button. I guess that would be viable but it feels like overkill. Is there some trick to using the layout editor to make the drop-down lists work? Other?

This forum topic shows how to call a FMScript from VB: http://fmforums.com/forum/showtopic.php?tid/195447/

Ok, makes a lot more sense now. I guess there are two basic approaches that come to my mind, and I'm not an expert here, one would be to find a way to have FileMaker periodically (e.g. every five minutes) suck in the XML file and the other is to find a way to have your application trigger a FileMaker script to run. I don't think either of those is built in. (Or perhaps there is a way with FileMaker Pro Server Advanced's XML interface to push the data in) Sorry I cannot help more, hopefully someone with a better sense on these issues can contribute.

Not 100% sure what you are trying to achieve, but if you want you can go right from VB (or VB.NET) into FileMaker via ODBC using ADO (or ADO.NET) you will need to install the ODBC drivers for FileMaker that came on the CD. Just an alternative way to "push", it would also eliminate the need to write the files to disk since you could go right from VB into the DB.