All Activity
This stream auto-updates
- Today
-
Is there a way to set up the SC app server to *not* serve documents via URL except when accessed through a Filemaker app? This would be a same server setup.
-
BuildMeSomething joined the community
-
On the Macintosh, you can change the page setup under the File > Page Setup
-
Script Schema for Tests of Fields instead of Hard-Coding Them
comment replied to Rich's topic in Script Workspace and Script Triggers
Are you really printing thousands of records at once? There are ways to speed up the loop, notably switching to Form view and freezing the window before starting. I have also seen suggestions to go to a field or use a blank layout, but I have never tested these myself. I am afraid you have lost me here (again). I think you want the calculation field to check if some of the required fields (in the same record) are empty? If so, this could be done simply by = Count ( FieldA ; FieldB ; FieldC ; FieldD ) < 4 This returns 1 (true) if one or more of the listed fields is empty, 0 (false) otherwise - and if you make the result type a number, you can use a summary field to sum it. However, keep in mind that a summary field must examine every record in the found set individually, same as a loop does*. So the difference in performance may not be that great - and if you're using this within a script anyway, I would definitely lean towards eliminating the fields and letting the script take on the entire task. -- (*) Actually, the loop here does not have to examine every record, because if you're looking for a binary result, you can exit the loop as soon as a faulty record is detected. -
I once printed some records to the Microsoft Print to Pdf. Now when ever I try to print a script, it defaults to Microsoft Print to Pdf, not my default LaserJet Pro. Is there a way to reset the device that scripts print to?
-
Script Schema for Tests of Fields instead of Hard-Coding Them
Rich replied to Rich's topic in Script Workspace and Script Triggers
Thanks, as always, for your reply. I apologize for my poor explanation skills. To put it succinctly, I want to avoid using dozens of test-for-IsEmpty-and summary fields (per table, and there are fourteen tables), just to give a go-/no-go flags for reports to print. You're right in that e-SQL's out since it would be problemetic dealing with found sets. I just finished making a looping script but there's a big performance hit when it comes to thousands of records to plow through. However, it just occurred to me while writing this post that a "simple" solution would be an extension of what I posted earlier, to have just two fields per table: one that would be a calculation field with a litany of Case statements to test for each report's minimum field requirement, and the other a summary field of the calculated field. I could then use a very simple script to act as a switchboard to select which report to run based on a global field that has a drop-down menu of report titles; the aforementioned calculation field's result will be based on what's in the global field. Time to get coding! -
Script Schema for Tests of Fields instead of Hard-Coding Them
comment replied to Rich's topic in Script Workspace and Script Triggers
I am struggling to understand what exactly are you asking here. - You can test if a specific field in the current record is empty or not by using the IsEmpty() function; - You can test if a specific field is empty in some records of the related set by comparing the results of Count ( Related::TheField ) with Count ( Related::MatchField ) - but this will not flag specific record/s with missing value/s; - I don't know of a way to do the same for records in the current found set, without defining a summary field that counts the field in question. Ostensibly, you could look at the result of ValueCount ( List ( Table::TheField ) ) - but this could backfire if the field can contain carriage returns. So if you don't want that summary field, you would need to loop across the found set, record by record. (In some specific cases you could use ExecuteSQL() to avoid the loop - but as a rule, ExecuteSQL() looks at all records in the table, so you would need to reproduce the find criteria within the calculation, which is hardly elegant and certainly not efficient.) --- A belated afterthought: The proper way to ensure that required fields are not left empty is through field validation. That would solve the problem at the source and save you the hassle of chasing the horses that had already fled the barn, so to speak. - Yesterday
-
Greets, all: I have a dozens of print-related scripts but I want to use scripts--instead of using scores of hard-coded calculation and summary fields--to test for the presence of values within fields both in same table and parent-child table fields--so if not all the required fields for a particular printout have values, either in the current record or anywhere in the found set, it's flagged and another script notifies the user that none of the (found) records meet the required minimum field value requirement. Is there an elegant way to do this all in scripts or am I stuck using calculation and summary fields? ( The calculation field sets up the test with a result of 1 or 0 and the summary field sums the calculated fields--if the sum is zero the user is shown the error message. TIA for your help!
-
'Resize to Fit' inconsistent?
Rich replied to Wickerman's topic in Script Workspace and Script Triggers
I'm having similar problems with Adjust Window in v17 (Mac)—the layouts won't resize even with Adjust Window [Resize to Fit] entered in a resizing script mulitple times. It also acts flaky between user privileges, too--Admin, no problem, but any other privilege and it won't work correctly. I found a temporary way around the problem by creating a standalone script that has just one instruction: Adjust Window [Resize to Fit]. So, in the resizing scripts I have, I've replaced the instruction Adjust Window with Perform Script [Resize_to_Fit] and that works. -
Help Pull the selected 2nd field values instead of 1st value
comment replied to chadski021's topic in Calculation Engine (Define Fields)
It makes no difference. A field set up to use a value list will contain values from the list. And the list includes values from the first field only. In theory at least, you could use a different calculation, using either a custom recursive function or ExecuteSQL() to get the result you want. But I doubt that would be a good way to solve whatever you're really trying to solve here. I suggest you rethink your entire approach. BTW, I don't see why you need your current calculation field at all. The two fields (checkbox and checkbox result) contain exactly the same thing. If you want to see the real contents of the checkbox field, place another instance of it on the layout and format it as edit box (and make it tall enough to see the entire list). -
As promised... With the new ScriptMaster which uses a sandboxed JVM it is now possible to load driver jars at runtime, whereas before they had to be installed in the Java/Ext folder This makes a function to tak to MS sql server possible. Sample code below... Get the jar file referenced from the Microsoft website. As this is from some production code we are using record as the column name for the record index, and the data comes back pipe delimited, but you easily change those... If you leave the find blank it will just return the id of the first record in the list. if you UPDATE it should return the number of rows affected, and hopefully if you INSERT it will give you an array of the record ids that were inserted - use at your own risk obviously... test, test, test. // MS_SQL( fm_query ; fm_find? ) // v4.5 // 18_10_14 JR // performs SQL query on MSsqlserver database and returns fm_find | record // fm_query is SQL statement e.g. SELECT * FROM Table // fm_find{optional} is list of field names to return in answer, and adds 'record' as last value - pipe delimited // requires mssql-jdbc-7.0.0.jre8.jar - this can now be loaded at runtime // assumes a column called 'record' as the ID import groovy.sql.Sql import java.sql.Statement import com.microsoft.sqlserver.jdbc.SQLServerDataSource t1 = System.currentTimeMillis() //edit this for your solution IDX = 'record' db = [server:'servernameorip', port:49513, databaseName:'name', username:'user', password:'pass'] ds = new SQLServerDataSource() ds.setUser(db.username) ds.setPassword(db.password) ds.setServerName(db.server) ds.setPortNumber(db.port) ds.setDatabaseName(db.databaseName) sql = ds.getConnection() Statement stmt = sql.createStatement() answer = al = ex = '' sqlt = fm_query[0..2].toUpperCase() try { if(sqlt == 'UPD') { //update ex = stmt.executeUpdate (fm_query) } else if(sqlt == 'INS') { //insert ex = stmt.executeUpdate(fm_query) } else { //select ex = stmt.executeQuery(fm_query) } //end if } catch (e) { return '{"error":-1,"error_message":e.getMessage()} } //end try if(sqlt == 'INS' || sqlt == 'UPD') { //insert or update sql.close() return ex } else { if(!fm_find) { //default find is 'record' id of first returned answer ex.next() answer = ex.getString(IDX) } else { sqlFind = fm_find.tokenize('\n') while (ex.next()) { for (i in 0..<sqlFind.size()){ //pipe delimited, change last character for \t etc. al = al + ex.getString("${sqlFind[i]}") + '|' }// end for answer = (answer? answer + '\n' : '') + al + ex.getString(IDX) al = '' } //end while } //end if } //end if sql.close() if(!answer) { return '{"error":-1,"error_message":"empty set"}' } else { t1 = System.currentTimeMillis() - t1 } //end if return answer.trim()
-
Help Pull the selected 2nd field values instead of 1st value
chadski021 replied to chadski021's topic in Calculation Engine (Define Fields)
Yeah i think its a bit complicated. By the way can these be done using dropdown. I mean on the value list i only show the 2nd field which is last name. but my calclucation result should be also the last name. currently the result is showing the 1st name. -
I hear ya. I was sure hoping we could help you identify it as well and maybe someone else here can. Funny person!!
-
Hi LaRetta, You are quite correct, there is no reason to allow creation from the QuoteItems table, I will rectify that. I suppose that because the problem is so odd to me I was sort of hoping for a "If you get struck by lightening and the dog farts at the same time while Venus is in decent" this sort of thing can happen. Thank you once again for your assistance though.
-
Then why is Allow Creation turned on for the Quotations side? Nonetheless, I don't believe that addresses your issue. Tables which must stay in balance, such as Invoice/LineItems or Quotations/QuoteItems should be handled transactionally and when created/edited on FMGo online or offline, it is critical good logging and error trapping be in place. Please see: Transactions. I doubt this is something which can be resolved from a forum post, unfortunately. Maybe your sync is failing in uploading new Quotations but uploads their QuoteItems.
-
Good day LaRetta, sorry for the delayed response but the electricity went yet again and has only just been restored. The reason for "copy" is that we use Mirrorsync to enable both on and offline record editing and creation and that is used as part of the set up. Every ID field has a copy which is actually the original on creation ID and the ID field that is visible is an auto enter calculation field from the original. The "Copy" fields are not visible or accessible on any user layout That was my thought as well because I can find no method of deleting the Quote without also deleting the QuoteItems. If however someone had actually managed to change the the QuoteId to break the relationship then the CutomerId and name would remain on the quote but that is not the case as the Customer is now not found in the Quotes table so the Quotes have actually been delete As stated the visible QuoteId is a calculated field from the "Copy" even if a user could create a quote from the QuoteItems table. The user never actually goes to the QuoteItems table and the QuoteItems are only available to them through the portal on the Quote Yes sorry, this one is indeed weird. For reasons known only to themselves the mines require a quarterly quote but a monthly service and invoice so we had to come up with a plan to carry a different QuoteId across three months invoices as well as our normal unique QuoteId. This is the first time that a Quote has gone walkabout in this manner after 14 months of testing and use Thank you for your assistance and insights into where to look for the problem
-
My Duyen joined the community
-
Sorry, this is a bit deceiving because I didn't finish my thought. We don't know your record-creation or duplication scripts or your auto-enter calculations nor have we seen your graph so we don't know where the breakdown happens but if an ID is improperly set or modified other than through that 'Allow Creation' relationship then the QuoteItems 'parent' could disappear (although the record would not actually be deleted). I again suggest you take a look at those duplicate 'copy' keys and also verify that you have the proper key fields on the layout if you have multiple table occurrences of those tables and be sure keys are set to 'prohibit modification of value' and set to 'unique'. If you create an empty clone of your file and post it, we might be able to spot the issue.
- Last week
-
How then are orphans created? You have IDs with 'copy' after them. Why? Is it possible that the parent records aren't deleted but rather their ID has been changed so they are no longer related? Do you prohibit modification of the primary key ( in field definition)? I also recommend adding detailed error trapping and logging, such as: Dan Smith's Logger. Robust logging can help you identify issues. Maybe until you identify the issue, don't delete but rather flag records for deletion and clean them up later. It is also possible that the __Quoteid Copy of Quotations is incorrectly holding the parent ID if the parent has been created from within QuoteItems ( since you have Allow Creation on the parent Quotations side). The __QuotidMine is also suspect. We do not have enough information to identify the issue. I suggest you hire a competent Developer to review your solution. And no, this isn't a plug since I'm not currently available. 🙂
-
Help Pull the selected 2nd field values instead of 1st value
comment replied to chadski021's topic in Calculation Engine (Define Fields)
That is the expected behavior: the checkbox field holds only selected values, and the selected values come from the value list which is defined to use values from the FirstName field. The second field is used only for display, to help the user select the correct value. Typically you would let the user see a name, and have the checkbox field store a unique ID. I don't know what the real problem here is, so I can't recommend a solution. Perhaps you need to calculate a full name first, then use that for your value list. Or use a different method of selection. -
Help Pull the selected 2nd field values instead of 1st value
chadski021 posted a topic in Calculation Engine (Define Fields)
Hi, What is the possible solution for this, See my attached sample. As you can see i selected Doe and Smith from the checkbox, But the result is John and James. It should be Doe and Smith also. Sorry for my bad grammaring i hope you understand. Example.fmp12 -
Hi (once again) LaRetta, Thank you for your response. The user can only delete a Quote from the iOS Quote layout using a scripted button, the script of which is very simple so I don't see how your suggestions could operate on the file and I believe that the relationship is set up correctly.
-
Hello to South Africa!! Three ways come to mind that it might happen (but there might be more): 1. You have indicated 'Delete related records in this table when a record is deleted in the other table' on the wrong side of a relationship. 2. You have a Go To Related[] step which fails to switch layouts or accidently isolates the parent Quote records of a record-set of QuoteItems and your script has then issued Delete All Records step on the Quote table. 3. Your User is in a portal and button issues a 'Delete Record' instead of a 'Delete Portal Row.' The easiest thing to check is the relationship. It's easy to accidentally specify the wrong side. It helps if you mark all your relationships by using text to add a plus or minus next to the occurrence where those options are specified. I also like to include an 's' to indicate there is a sort. In this way, it will be easier to spot the culprit. As for #2 and #3, that would require a bit of detective work. If you use GTRR[] and it is not *properly error trapped, it can fail and if one of your scripts then issues a 'Delete Records', you again can be on parent record set. I hope it's #1 since you can then fix the issue quickly and still enjoy your weekend. * properly error trapped ... if you test for Get ( LastError ) or 101 (record is missing) but you issue a 'match found set', it can fail and not switch layouts. You must instead trap with 401 to catch it.
-
Miyuki Imaizumi joined the community
-
Hi All, I have a database where the relationship between the Quotes table and the QuoteItems table is set up such that if a record is deleted in the Quotes table, the related records in the QuoteItems table are also deleted. I have now been shown an instance on one Customer where there are four Quotes missing from the Quotes table but all the QuoteItems are still in the QuoteItems table. How can this be done, I am unable to recreate a circumstance where I can delete a quote and leave the quoteitems in place. Any help appreciated
-
Autopopulate Fields based on a key
comment replied to skoothor's topic in Interface Design Discussions
It is difficult to advise without understanding what exactly is your solution supposed to track in real life, and for what purpose. One thing jumps out immediately, though: you have numbered fields - and that is an unmistakable sign of poor structure. Apparently, one team has many members - and this should be handled by two separate tables - Teams and Members, linked by TeamID (a meaningless value, typically generated as an auto-entered serial number in the Teams table). This is assuming a member belongs to one team only (or at least to only one team at a time, and there is no need to keep record of members' team assignment history). Next, we have performances. I am guessing from your description that this means team performances, not performances of individual members. This would also be a one-to-many relationship between the Teams and Performances tables - again linked by TeamID, and by TeamID only. If it's important to know who the team members were at the time of recording a performance, then you would need a field in the Performances table to auto-enter a list of the current MemberID values, so that you can use it in a relationship to (another occurrence of) the Members table. Otherwise you can simply display the related details (e.g. member names) directly from the Members table on a layout of Performances. -
Autopopulate Fields based on a key
Steve Martino replied to skoothor's topic in Interface Design Discussions
That's not an ideal set up. Best to use standard primary key (Team Table, related to a foreign key (Performance Table) set up (check 'allow creation...' in Performance. The primary key should be an auto-enter serial number, that's never seen on the user interface, or could be edited by any user. For performance records, why not just a portal of Performance on the Team Table? Then entering any data in the portal (i.e.performance date) automatically creates the related record. If you don't like a portal for entry, then maybe a card window. A simple script would put the primary key into a variable, open the card window layout based on a performance TO, new record request, set foreign key to the variable (foreign key field doesn't have to be on the layout). When the user is done enter a new record, a Save button can perform a 'Save Records' and close window, or Cancel button can revert record (or delete) and close window. -
Having made the connection to Google's Services using Oauth 2, and spending a touch of time investigating how we can discover documents within Google Drive, we're in the home stretch of reading and writing data to a Google Sheet. By simplifying the way we make API calls to any of Google's services, we end up with a great solution for working with any of Google's available APIs. Also, by focusing on the simple example of working with one simple Google Sheet, we end up capturing data via a Google Form which dumps data into the Google Sheet. All we need to do is read and write to that sheet. This video covers the topic of watching for an expired access token and making sure that each call to the API has the greatest chance of success. Within no time you'll be reading and writing to a Google Sheet as if everything was right there within FileMaker Pro itself! Click the title or link to this article to view the video. View the full article
-
-
-
Who's Online 2 Members, 0 Anonymous, 130 Guests (See full list)
-
-
-
Blog Entries
-
