databaser

Say for example I had 100,000 employees who all make widgets every day. I have a record for each employee's productivity on each day. If Bob has worked 10,000 days, I have 10,000 records for Bob. Field A is the number of widgets and Field B is the date. I want to add a field to each record that calculates the earliest date where the employee made as many or more than the number of widgets he made on that day. If Bob made 200 widgets on 1/1/2020, and the first time time he ever made at least 200 widgets in a day was 5/5/2018, then I want a field that shows 5/5/2018 in his record for 1/1/2020. I know I can write a looping script that searches for Bob records with >=200 and sorts it by date and then sets a variable for the date in the first record, goes back to the original record, and sets the new field with the variable. I'm dealing with tens of millions of records though and it could take weeks for a script to fill all that in. This is what I've been doing and it's taking nearly a whole second to process each record. Of course, I assume there's ways to do this with related tables, possibly a self join. I haven't figured it out yet.

i'm not sure i undestand, but here's what i'm thinking create a global field 'g1', a calculation field 'c1', and a summary field 's1' have a script enter the new serial number in g1 the calculation for c1 is g1=serial summary is the total of c1 have a script trigger for when you exit the serial field - the script will set g1 as that serial number., then if s1>1 it deletes that record and goes to the record that already has that serial

It's not great, but I think I just thought of something I could (maybe with scripts / script triggers or something) break down records (perhaps duplicate them into another table for this purpose) so that the records continue each other and are related. maybe 2 lines each. then you force them to sort properly and you kind of have it, but the other fields will keep getting duplicated, but I can put them on the side and out of the way. Any better ideas?

Filemaker really has some deficiencies that annoy the cr** out of me. Is this possible, either literally or just 'effectively' (or creatively, using non-layout techniques (I don't know), etc)? I want to have a list layout where one of the fields can sometimes contain a lot of text, or sometimes very little. I really wish the height of this field object would vary to accommodate the amount of text in the field for each record without really going over (or at least have reasonable proportionality - if I have to formulate a way to do it pretty closely based on character count, so be it). (So I can use screen-space well, without having to scroll inside of records). Then, of course, I want the height of the layout part itself to vary to accommodate the height of that field. I think FM is actually quite weak on layouts (that I feel like I'm really dreaming here that there could be a way to accomplish this), which is disappointing, because it's so gui too - what a bad combination. ### I'm using windows at the moment, with 11 advanced and 12 advanced installed (I think I still prefer 11, but if 12 could accomplish this better, by all means) ### Also, I might even be willing to buy an extension or something if that would help. ### It's just so annoying, they could have just allowed you to define sizes in layouts with calculations. What would have been so hard about allowing that? I could have figured out how to proportion things properly based on character counts and stuff.

I have several solutions for which I would use this. A simple one, is I might want to have a filemaker solution run in an amazon cloud instance, and have it constantly check a website for changes/updates, and send me an email alert in the event of certain changes/updates. (this is for a unique purpose, not something like stock prices, so there would be no service I could use to do this for me). I have filemaker 12 advanced, but I've still been using 11, just because I'm used to it. I'd be willing to switch though. My objection to using web viewer is fear that it will over-consume cpu and ram, and be slow. There are some solutions for which I need this to be as quick as possible (the above isn't really one of them).

I posted this quite a long time ago and got no replies, so I suspect my only options are to buy webassistant or use web viewer and the getlayoutobjectattribute function. Does anyone have input on whether the getlayoutobjectattribute fuction can be just as good. I'm thinking I would just disable images and flash/plug-ins for internet explorer (this will be on a PC), and then maybe it would be as quick as using web assistant.

I don't have a carriage return symbol handy, but it's that thing you click that looks like a P. This should do it: substitute (field ; "x " ; "x[carriage return symbol]")

I think I was able to solve this by creating a related table with two fields and importing a folder with all the text files (one field for the file names and one for the contents). I'm still disappointed that, as far as I can tell, this can't be easily done within a script.

Is it possible to have a script import the contents of a text file into a text field in a script? If this isn't directly possible, are there any neat tricks/hacks/plug ins, that effectively make it possible? (I don't want to create new records, I want to import corresponding data in text files into existing records. For example if field "A" is "Blue", I want to be able to have a script set text field "B" as the contents of C:/Blue.txt) I need this to be done to thousands of records. Also, I have no problem changing the extensions of the txt files to something else, if that would help.

360 works' web assistant extension has a function - wageturl - that gets the source code of a URL. webassistant costs $99 though (or the free version only works if filemaker has been open for less than 2 hours). Is there anyway to accomplish this without spending $99? I'm familiar with the method of using a web viewer and the getlayoutobjectattribute function, but I don't want to use that method. Is there anything else? (not using a web veiwer)

This would solve it... First, create the following 5 custom functions, in the following order: SingleBreaks(text) ----------------------------------- If (PatternCount (text ; ¶ & ¶)>0 ; singlebreaks(Substitute (text ; ¶ & ¶; ¶)) ; text) #################################### NoTrailingBreaks(text) ------------------------------------ If (Left (text; 1) ≠ ¶; If(Right(text ; 1) ≠ ¶; text; Left(text ; Length(text)-1) ); Middle(text ; 2 ; Length(text)-(1+(Right(text ; 1)= ¶)) )) ##################################### CleanupBreaks(text) ------------------------------------- notrailingbreaks(singlebreaks(text)) ##################################### RemoveDuplicatesSub(values) ------------------------------------- If( ValueCount(values)>1 ; removeduplicatessub(notrailingbreaks(LeftValues(values ;ValueCount(values)-1))) & ¶) & If(PatternCount(¶ & Substitute(values;¶;"¶¶" )& ¶; ¶&GetValue(values;ValueCount(values)) & ¶)<2; GetValue(values; ValueCount(values))) ##################################### RemoveDuplicates ------------------------------------- cleanupbreaks(removeduplicatessub(values)) ##################################### Then just change your calculation to Substitute ( RemoveDuplicates(List ( Assessment Questions::Hazard No )) ; ¶ ; " " )

let(a=field1+field2+field3+field4; case(a=0;""; a=1 ; case(field1 ; "field1"; field2 ; "field2"; field3; "field3" ; field4 ; "field4"); let(b= if (field1; "field1, ") & if(field2; "field2, ") & if (field3; "field3, ") & if(field4 ; "field4, "); left(b ; length(-2) ) ) ) It's not clear what he's asking, but that would be an answer, if he means he has four fields where a value of 1 is either checked or unchecked. It's not the shortest way to write, but I think it's the way to write it that would calculate as quickly as possible.

Thanks comment. I might not have done it the best way, but your suggestion has doubled the speed. I created a new table with 500 records and a field ("order") where each record is numbered from 1 to 500. Now I have it put the list of 500 numbers into a global field, and have a calculation field ("value") of getvalue(globallistfield ; order). Then I just sort records by the value field. I also had it subsequently find the weighted percentile by using the value in "order" to determine the weight of each value and going through the records in ascending order, until the weights aggregate to the desired percentile. This seems to be 2-2.25 faster than what I was doing before. On another note, I'm curious as to whether speed could be improved by using Filemaker Pro with Amazon EC2. Does anyone have any input on that?

The overwhelming majority of time spent performing all my calculations is attributed to one thing, that ideally, I would like it to do 30,000 times per day (I may be able to make due with 3k, 7k, or 10k times per day, but 30k times per day would be ideal for my purposes). This one thing, is to take a new list of 500 numbers, and determine a 'weighted' percentile. This is not how I compute it, but it's essentially like a percentile where you duplicate the numbers different amounts of times; the numbers are listed in a chronological order, and it would be like if the first one were duplicated 5 times (if 5 were the starting weight), then the next one 6 times... and the 500th one 504 times. Even with the best optimization, would it be possible to do that 10,000 times in 4 hours? Could google spreadsheets do it much faster than filemaker? ### Update: I did notice that google spreadsheets has a percentile function - and it seems to come up with a percentile on 500 numbers much faster than filemaker can do an ascending sort of 500 numbers. I'm not sure about trying to weight it though, in google spreadsheets.

I think I'm really wondering if google spreadsheets would be able to perform these calculations much quicker. If yes, I think I would just seek to hire someone to help me do this.