flutegirl

To try to explain the setup as succinctly as possible: Database is running on a FM17 Server (on a Windows virtual(?) machine... Our IT department handles that part). The FM database does a number of imports every morning from our primary (4D) database, using an ODBC driver, but I have not found a way to have those imports run successfully from the server, so I have them running on an instance on a secondary user account on my desktop computer. The scripts are triggered by adding a record to a table, which is checked every five minutes using an OnTimer script. I run a separate instance of FM17 on my user account so that I can use the database even if there is an import running. This solution has been working (fairly) well for a number of years, but last week, my desktop computer was updated to Mojave (10.14.6), and since that upgrade, it seems that the imports on the secondary account will only run while that user account is active. 1) Is anyone else doing anything similar, where they have an instance of FM running on a separate user account? 2) If so, have you had any trouble with Mojave, or have suggestions on what may be going on? 3) Any suggestions on how to fix this? My initial thought is to just open the database using FMP on the Server system and try having the imports run from there, but am concerned that might also have problems. Any suggestions would be greatly appreciated.

Thanks - I didn't even think of troubleshooting it from that direction. Hopefully that will help me find the problem.

I know this is a fairly old thread, but I'm struggling with this right now as well, and am running into some of the issues you're referring to here. I've been trying to create a style sheet that will allow me to export from Filemaker with custom, user-friendly headers, and keep running into issues with Excel telling me the resulting document is not valid. On a whim, I attempted to open the file in LibreOffice, and was a bit irritated to find that it opens just fine, and then once I save it from LibreOffice as a .xls file, THEN Excel will open it. I'm curious if you anyone has any ideas why this is, and how I can get Excel for Mac (latest version) to open these files directly, as I can't risk sending out files that won't open in Excel.

Ah, some more testing (and getting some others here involved) found the solution. The ODBC driver was set up as a System DSN. When switched to a User DSN, it is no longer requiring admin password. Unfortunately, that doesn't help on my workstation, which has two user accounts, as I can't seem to install the same driver as a User DSN under both accounts.

Our main database is a 4D database, but we use Filemaker to handle a number of "helper" applications. In order to access the data in our 4D system, we've installed ODBC Manager on a few of our workstations, and installed the 4D v13 driver from 4D's website. In general, this all seems to be working well. However, as we've begun doing more with this integration, I've run into an issue that as part of the import, the OS is asking for an administrator's name and password to allow Filemaker to make changes. The main user account I use when developing is an Admin account, so I've never run into this before. Does anyone have any experience with this, or have any ideas on how to avoid the OS requesting an admin account password to perform the import? Interestingly, this only happens the first time an ODBC import is attempted during any session of Filemaker. Filemaker Pro v13 OSX 10.9.x

When the user clicks the "Paid" button, will you be populating a field to notate that the account was paid for that year? If so, you should be able to add a criteria to the conditional formatting to show the text area if dates are within range AND Paid is blank or less than a value, or something. (Of course, that means you'd have to have some intelligence built in so an account marked paid for this year doesn't remain marked paid next year.)

I am looking for a way to avoid the user to close a popup without clicking a button in the popover (thereby running a script) so I can use the popover for data entry. My searching capabilities seem a bit lacking today, as I couldn't find any previous discussions on this topic. I've come up with a solution, but I thought I'd check here and see if anyone had any input or a better way to do this. My solution: I created a script "Abort Click-out" The script is simply: If [ Get ( ScriptParameter ) = ""] Exit Script [Result: False] End If Then, in the popover, I do a script trigger of: OnObjectExit: Abort Click-out; Parameter: Get(ScriptName) In this way, if a script is running, then Filemaker passes the script name to the Abort Click-out script and allows the script to exit normally, closing the popover. However, if the user just tries to click out of the popover, no script parameter is sent, so the script exits with a result of false so the popover doesn't close. Are there situations I'm not thinking of that will cause problems or break something when handling it this way? Any suggestions for a better way to do this? Any advice would be appreciated. Thanks!

Hello, all. Â I am attempting to make a layout in Filemaker that will be a dashboard report of sorts, comparing one time period's sales to a previous time period (user definable). Â I have been able to gather the necessary information and display it as text on the layout with no problem. Â However, what I also want to do is to create a few charts on the page which will display the relationship for a single criteria across the two time periods, but I want to have 4 or 5 of these charts on the page. Â The problem I'm having isn't creating the chart, it's the fact that I can't seem to format the chart - no matter what I do, I end up with a chart that has a ton of white space around it and the Y-axis values are displayed on both sides of the chart. Â This is leaving the actual columns that are displaying the data as a rather small graphic in the midst of a lot of white. Â Is there any way to have more control over the way the chart is displayed in Filemaker? My Google-fu is weak today, and I have not been able to find anything of assistance when I have looked. Â I've attached two snapshots - one of what I've been able to make in Filemaker, and one that is more of what I'm looking for, but created in Excel. Â Any help would be appreciated.

Thanks, that fixed it. Found a couple problems as I looked closer at the script, but this fixed the main issue. I will not be able to control the users as well as I'd like in this particular spot (they can add a product in the portal, and I can't be certain that they've committed the data before they click to run the script I'm calling), so I had put a Commit Records script step at the beginning of the script. I changed that so the first thing I'm doing is moving to a field and grabbing the product ID before I commit the record, and that fixes it. (Well, after I fixed another logic error I had in a subscript.) Thanks for your assistance! This probably would have worked better, except my script is a bit scary right now doing too many things. Had I thought this through before writing it, I would have broken it down into 2-4 subscripts that would have made this much easier (and I could have handled it all via script parameters), but as I now have it working, I don't want to go back and clean it up. I'll probably regret that decision next spring when I have to dig out this database again. ...or next week if something else breaks and I end up rewriting the script anyway.

Not entirely sure how to explain this, but I have a layout with a portal. Most of the fields displayed in the portal are set as non-enterable while in browse mode, as the data is coming from a table that should not be edited. I have a script in the portal that needs to take the product ID from the portal and create a new record in another table, but there are times when I've noticed it creating a record for the wrong product ID. It seems that what is happening is that when I click into an editable field FileMaker properly identifies the record I am on and properly picks up the Product ID I want. However, if I just click on the button in the portal, or click on one of the fields that is not editable, the line itself highlights, but the record is not being selected, so the script runs having grabbed the Product ID from the first row of the portal, instead of the row I clicked. I don't want to require the user to click into an editable field before running the script, but I can't figure out how else to get the portal to know which row the script is supposed to run from. Help?

Yeah, I should have written my example that way - I use that when I actually write the scripts (and am staring at the list of script steps), but when I'm thinking through a problem, I tend to forget about it and think in straight-up If/End If statements. Related question - if I just end up doing this as the long string of If/Else If/Else If/End If statements (there will probably be 10-15 of them right now... Well, that's a lot for me!), will Filemaker have to evaluate every single If, or will it just evaluate until it finds the matching one, and then skip over all the remaining else's? I'm sure since it's only a couple handfuls of possible criteria, there would be no appreciable difference in performance, but I'm trying to make sure I'm building this database in ways that will teach me best practices as well, and I'm sure if this has to evaluate every single statement, that's not a good way to make a scalable solution.

I am running a database on FileMaker Server 12, and have a number of daily imports and scripts that need to be run, but due to a limitation in the ODBC driver (connecting to 4D. Server can't run the imports, it has to be done on my client machine), I can't set up a scheduled script on the Server to run the imports. The thought I had on how to get around that was to have a scheduled script on Server which would populate a "Script Request" table, which would simply add a record to a table with when the script is requested, which script is requested, and the times that it can be run. Then, I'd set up an OnTimer script on my client machine that looks at that table for any records which don't have a completed stamp, would pull the name of the script from the field, and run that script. However, unlike being able to go to layout by calculation or set field by calculation, I don't see a way to run a script by calculation. The only way I see to do this is to write one big script that goes through a bunch of If [scriptToRun = "import"] Perform Script [import] end if if [scriptToRun = "import 2"] Perform Script [import 2] end if etc. So, I have two questions: 1) Is there something I'm missing that would be a better solution than all of this? 2) If not, is there a way for me to optimize this so I don't have to remember to update multiple scripts each time I need to create a new import?

Oops, I think I figured out my problem. I had the table sorted on the wrong field. It does still take a little while to update when I do a new search now, but only when my search is over a large selection - when I do a tiny subset, it's lightning fast.

So I'm trying out the Fast Summaries method, but I'm getting stuck on one thing: When I go to the Line items layout in order to do the find, Filemaker gets stuck trying to update the two summary fields - on nearly 6 million records. I hoped that not including the summary field on the layout would keep that from happening, but that doesn't seem to have an effect. Is there something I'm missing that would allow me to utilize this method without FileMaker trying to update the summary fields before the GetSummary function is called?

I have a layout with a Tab Control on it, housing 3 tabs. In each tab, I have a portal, all pointing to the same table occurrence, but displaying different data. 90% of the time, everything works fine. However, some of the time, when I click on a tab to switch to the data I want, the view switches to show everything as <Table Missing>, and sometimes, clicking on the 2nd or 3rd tab takes me to the 1st tab showing <Table Missing>. A second click on the desired tab causes the portal to refresh and show the proper data, but I can't figure out what's going wrong in the first place to fix it. Anyone have any ideas on how this can be fixed?

You could also just use a script parameter on the button - not as slick as having the button tell the script which it is, but it accomplishes the same thing. Your Set Variable would then be Set Variable [$abc; Value:Get(ScriptParameter)]

Thanks, I'll take a look.

I have an SQL query I am trying to run through the ExecuteSQL query. The problem I'm having (I think) is that because it's running off a table with over 5 million rows and I'm trying to grab a sum, not just the data, each query is taking anywhere from 30-60 seconds to run - and during this script, I have to run the query up to 36 times (monthly sums going back three years), so that causes this report to take too long in my opinion. I *think* I would be able to speed it up if I can do the heavy lifting (i.e. the sum) across a subset of the 5 million rows, and logic tells me that I should be able to do that by using a 2nd Select statement (select sum(my field) from (select (subset of records) from Table)), but I can't seem to get anything to work within the ExecuteSQL statement. Multiple web searches have returned no usable results for me, so I'm not sure if I'm searching for the wrong thing, or if ExecuteSQL just can't handle this. Does anyone know of a way for me to be able to accomplish this, or a if I'm just totally barking up the wrong tree?

In this particular instance, I need a self-contained database that will be distributed to people for use on an iPad while at a trade show. I cannot point them to the main data file that is on a Filemaker 12 server, because that server is only accessible from our internal network, not everyone has cellular iPads, and a wi-fi connection cannot be assumed at the trade show. So, what I'm trying to do is grab summary data so they can have some quick reports at their fingertips during the tradeshow.

A few months ago, I was introduced to SQL queries, and have been using them quite a bit recently (including the ExecuteSQL function. I think it's my new best friend!) However, as is often the case with a new tool in my repertoire, I'm suddenly seeing everything in the context of "Oooh, this would be *simple* to do as an SQL query!". Because of that, I'm finding myself on a regular basis wanting to import records from one Filemaker table (or database) into another using an SQL query, but I can't figure out how to do this. The ExecuteSQL function seems to be wonderful for pulling data into a field in a table, but I'm needing to actually import new records. I'm finding references to setting up Filemaker as an ODBC data source, but I can't quite figure out how to do this. Is this even possible to do? If so, can someone point me in the right direction to be able to implement this? Thanks!

Hopefully I can explain my question well enough for you to understand what I am doing and help. I have a database that I created for in-house use. Currently, it lives as a collection of 4 databases (oops)... A Launch Bar file (for lack of a better term), two Report files (there's my oops... Should have ensured all reports were done in the same file), and a Data file. After attending a Filemaker Training Series class in November, I have realized a number of places where I need to clean up my scripting, calculations, and lots of other stuff (the joys of being self-taught!), and as part of that, I am working to integrate the Launch Bar file and the two Report files into a single UI file, while leaving the Data file (mostly) alone. What I'm wondering about, though, is how to handle the scripts that update the Data file on a daily basis. I'm working with one handicap in that our main database is in 4D, and I have to import data via an ODBC driver - I don't have a live connection, and from the research I've done, there's not even a way for me to have the server where the files are hosted run these imports, as the ODBC driver doesn't seem to support server-side imports. Because of that, I have an iCal reminder set up every day with an Applescript that calls and runs a script in my UI file and runs through whichever imports/reports need to be run on that given day of the week. Most of the imports are run from that UI file as a call to run a script from the Data file. I'm wondering, though, if adding a TO from the Data file and creating a basic layout in the UI file will allow me to create a script in the UI file and do the import from there, without actually going to the Data file. Secondly, if this is possible, is it advisable? Hopefully my question makes sense. Any insight would be appreciated.

Thanks for the assistance. As an explanation of the global fields, here's what I'm trying to do: In the UI file, I am trying to create a report generator. The way I'm attempting to do that is I have a table (Report Data) which has a field for the Product Type(s) I want reported on, as well as fields for the results of the relationship lookup. A second table (Called Globals, but just contains a single record and is connected to the other table occurrences by the [ X ] relationship I can never remember the proper name of) contains the date ranges I want to compare. My script is supposed to loop through the records in this table and, by way of the relationship I'm having issues with, return the sum of sales over the various date ranges requested and enter those values into the fields in the Report Data record for that Product Type. As I was having issues directly passing the Product Type name and the date ranges to the Data file via the relationships, I've instead created a script in the Data file passes the Product Type via a script Parameter, and then uses Set Field to populate the global field in a Lookups table. The Line Items::Product Type is then evaluating that Lookup::Product Type field to populate the field with either "All" or the Product Type, as a blank value in the UI file means I want all values, not blanks. I tried changing Lookup::Product Type to a non-global (like I have above for Report Data) and it still didn't seem to work, so I took the calculation out altogether, and it is returning the results I would expect. As to why I wanted that to be a calculation field, for this specific report it's not needed, but I was trying to make this robust enough that I can use the same general framework for the next report I need to do, in which I want to be able to select values for any (or all) of six different categorization fields and have the report locate matching records to do a sum. The problem is that if I select a value for only one field, I need all values of the other five to also match. I was attempting to do this by putting the search criteria in a global field, then having calculation fields in the Line Items table which would test the global field, if blank, enter "All", otherwise, enter the correct value for that item. This would happen in the UI file as well. I would then have a relationship that is checking against all six fields to find the related records so I can get a sum of the sales value. Maybe there's a better way to accomplish this relationship? At least I can take care of my current need by simply looking up the data on all records, since there will only need to be a single match field besides the date. Thanks again for your input and assistance.

I am working on something, and can't seem to figure out why this isn't working. My solution uses two files - the data file and the UI file. I've connected the two by adding the data file as an External Data Source (Filemaker). What I am trying to do is have my UI file go to the data file and give me the sum of matching records. However, when I use the product name field as the match, I am getting results, but if I attempt to switch to the product type or product category, I get no results. The data table is called Line Items. In it is stored (along with some other data) the line item ID#, the product id and product name, the quantity, unit cost, extended cost, and date the unit was shipped. I have a relationship set up from the UI file where: UI File::Item ID = Line Item::Item ID AND UI File::StartDate ≤ Line Item::Ship Date AND UI File::EndDate ≥ Line Item::Ship Date I then have a calculation field in the UI File that goes through the above relationship and gives me Sum(Line Item::Extended Cost) The above relationship works. However, when I try to change the first line of the relationship to UI File::ProductType = Line Item::Product Type things break. the Line Item::Product Type field is a calculation field that is looking at the value of a global field in another table (the lookup value), and returning either "All" if the global field is blank (because that means I want to see all product types), or the Product Type of that item (from the Inventory table) if there is a value. I have double checked and the Line Item::Product Type calculation is set to text, I have a relationship in the data file from Line Items to Inventory (which is working properly, as it's returning the proper results for the Line Item::Product Type calculation field), and have checked and re-checked the relationship in the graph from the UI file, and I cannot figure out why this isn't working. Is there something I'm missing here?

No, I don't have control over the format of the received data. I have two exports to use: 1) Inventory data - this includes a lot of extraneous data I don't need, but I will pull from it two fields: Product Code and Current Inventory Value 2) Sales Data - this has a single line for each Product Code, and provides me with the gross sales value, gross profit, cost of goods sold, and a few other bits of data I probably won't use. I'm finding that while I understand how to structure the database as eos suggests, I'm struggling at the point of trying to figure out what to do with the data once I populate the tables, how to get the summaries that I'm looking for, and how to present it in a reasonable form for the users. I was hoping that my previous vague explanation would be helpful and get me pointed in the right direction, but it doesn't seem to be, so let me try to explain in more detail - if for no other reason, maybe I can understand what I'm trying to do a bit better. I am attempting to create a report for the buyers at my company that shows them the historical sales and inventory data. They need to be able to select any combination of: Brand Vendor Buyer Department Category Product Type to be able to see a summary of all products that match those results. For example, Buyer A may want to run a few reports: Items for Vendor A Items for Vendor A under Buyer A Items for Vendor A in Category B The way I'm currently doing this is with a huge Excel file. I have two sheets in the workbook - one for historic sales by product name (each row is for a single product code, and has columns defining the brand, vendor, buyer, department, category, and type, as well as an individual column for gross sales and cost of goods sold for every month for the past 3 years) and one for historic inventory by product name (again, each row has columns for brand, vendor, etc, and a column for the inventory value every month for the past three years.) On the main sheet of the Excel workbook, I have provided space for the buyer to enter the Brand, Vendor, etc, they want to review. The two historic sheets have a column which is a match calculation, and marks any row where the Brand, Vendor, etc matches what is selected on the main page. Then I have formulas on the main page which look for lines with that match field marked and do a sumif() function to subtotal all matched lines and display it. There are also additional calculations to take the summary data of gross sales and cogs to determine profit, turns, GMROI, change over last year, etc. Probably about 10 calculations based on the three values I store in those two other sheets. The problem with the Excel file is that it takes about a minute to update each time the criteria is updated. It is also very difficult to update. The entire building process of the Excel file made me feel like I was using a spreadsheet to do a database's job, but I'm starting to question that now, because I can't seem to figure out how to get the same functionality in the database, even though I know that the database would be better able to store and update the data. I'm afraid I may have locked myself into thinking about this as a spreadsheet, which is sending me down the wrong path to get this into a database, but I'm at a loss for how to move forward to pull the data into a recognizable form. Maybe I'm wrong and a spreadsheet is the proper medium for this particular report?

Somehow, I thought that would be the answer. For a bit of further clarification, I'm getting this data as an export from our production system (which is not in Filemaker), so do not have the transaction data - just the monthly summary by item. If I understand your answer, the solution you would propose would store all of the sales/inventory data in a table such as: Historical_Data ::ProductName ::Month ::Year ::Value ::DataType (Inventory or Sales) I get that, and understand the benefits in terms of data storage. Unfortunately, my lack of training on databases is showing itself because I can't make the necessary logical connections between storing the data in a table like this and being able to extract the data I need to do the calculations and sums I need. Specifically, I'll end up having the user enter one or more criteria for Brand, Vendor, Product Type, or a few other fields. I then need to sum for each month the Inventory value and Sales (our cost and the price the customer was charged) of all Products that match that criteria, and I'm just not sure how best to do that. Should I be creating another table that captures that data with a calculation field? Should I be using a script to do calculations and populate fields with that data? I'll continue pondering this and trying a few ideas. Thanks for the input, so I can at least head in a proper direction.