Jump to content

gerg nnud

  • Content Count

  • Joined

  • Last visited

Community Reputation

0 Neutral

About gerg nnud

  • Rank

Recent Profile Visitors

1,890 profile views
  1. You just turned on the light bulb I was looking for and answered my post precisely! Thought I had tried that but hey... tested and works. So I can relate the SQL query to ANY table using this syntax. Yes! I can now delete a whole lot of relationships in my main solution and structure more value lists using SQL. I really appreciate your input. Thank you. If you have any time I would still like you to have a look at my solution to date and tell me what you think. Don't hold back any punches. If it is of any merit I would like to 'give back' if I can. Oh. One Other Thing. I have been used to using the pipe "|" symbol in field and table naming i.e. Lists|Custom. SQL seems to not like that. Am I correct?
  2. Thanks Bruce I call the table 'Global' only because it contains one record only. A lot of those fields are not actually global fields because a value list cannot come from an unindexed field. Maybe I should call it 'Utility' or something but that is not the point I was wishing to discuss. My point is more about how to refer to a value in another table in the SQL syntax i.e. ExecuteSQL ( "SELECT List FROM ListsCustom WHERE Label = ?" ; "" ; "¶" ; SomeOtherTable::Cust_List[3] ) in my NOOB speak. Of course that doesn't work. I do understand the paramerterized form you suggest however when trying it that way it doesn't work. I have gone ahead with having the Cust_List field directly in the ListCustom table and managed to get everything to work but I think my code is still clunky. You might be kind enough to have look at the rough file itself and see if you can point out how it could be improved? If it was robust enough I think it would be a good little module to post on Modular Filemaker as I have not seen anything like it out there and I believe it could be of use to many. CustomListDemo.fmp12.zip
  3. I'm a complete newbie with SQL, just exploring now. This is probably a stupid simple question but here goes. I have a table I want to query to extract a virtual value list. I want the WHERE condition to come from a value in a global table. I've tried lots of things but just don't have the knowledge of the syntax in SQL. cList1 = ExecuteSQL ( "SELECT List FROM ListsCustom JOIN Global ON _pk_1 = _pk_1 WHERE Label = Cust_List " ; "" ; "¶" ) cList2 = ExecuteSQL ( "SELECT List FROM ListsCustom JOIN Global ON _pk_1 = _pk_1 WHERE Label = Cust_List[2] " ; "" ; "¶" ) cList3 = ExecuteSQL ( "SELECT List FROM ListsCustom JOIN Global ON _pk_1 = _pk_1 WHERE Label = Cust_List[3] " ; "" ; "¶" ) So in the above 'Label' is the field in the 'ListsCustom' table that provides the condition for generating the virtual value list and 'Cust_List' is the global repeating field in the 'Global' table that provides the three list labels. If I put Cust_List in the ListCustom table then this works. cList1 = ExecuteSQL ( "SELECT List FROM ListsCustom WHERE Label = Cust_List " ; "" ; "¶" ) ...but for other reasons I need the Cust_List field in the Global table. Hope someone can shortcut this for me.
  4. Thanks Comment for piping in. I just tried a few more tests along those lines. I'm doing the 'find' by a go to related record script step. I then do an import from that specific TO in another script step. So I just tried a manual import to the table that holds the portal but still all records, not the one related one. I have even tried using two unrelated tables within the db and a manual import attempt as another test with the same results (all records, not the found set). That works in any other db I have here. If I import from another db it works as expected, just not table to table in this db. I have had this working at some stage in development, I have applied this technique on many occasions but it does seem something is mysteriously wrong. I have also tried recovering the db. No go. In all other respects the db seems healthy. Any other thoughts?
  5. I am trying to copy selected line item records from one table to another as part of an intended 'recurring transactions' structure. For some reason if I perform a find on line items for that transaction and attempt to import into the recurring line items table it wants to import all records (12000) and not the found set (1-20) as I would expect. I have just conducted some tests with other databases to confirm I have an issue with this database only. The test databases would only import the found set from table to table as expected. Is my db corrupt or is there some setting somewhere thats changes this behaviour? Any help would be appreciated. FM13.
  6. Thanks dwdata Yes, I have anchor buoy relationship structure but find it doesn't go both ways sometimes when using go to related record. Trying to use the one relationship does not allow the selection of the layout I want to display unless I put the same relationship in the other way. I have put the repeating fields idea in the trash.
  7. Now I know you are all going to say “don’t use repeating fields to store editable data!” but I am running into all sorts of other problems with my solution using a separate table of records that I thought it may just be that repeating fields could offer a solution. Contacts table with multiple addresses. Pop List for field AddressType based on editable custom value list - Address 1 street, Address 2 Postal, Address 3 Billing, Address 4 - Shipping, Address 5 Other …Edit to generate a dynamic relationship. The current solution uses a separate Contact_Addresses table and the dynamic relationship is based on a calculation field in Contacts - AddressTypeAbs = Abs(AddressType), which returns just the numeric value from the value list. A transparent portal then displays the related address fields on the Contacts layout. This is an elegant way to save space on the Contacts’ layout as only one address is displayed at any one time but unlimited addresses are possible. My problem is however that many other tables in my solution have a relationship to Contacts, e.g. Invoices, Purchases, Quotes, Personnel and on and on and on. So in order to display address details in these other tables each has to have another relationship to Contact_Adresses. The deeper it goes the more complex it gets with relationships, workaround scripts and key fields. I thought that maybe using repeating address fields in the Contacts table itself might simplify the database by drastically reducing the need for the extra relationships, key fields etc. but I cannot seem to find a way of achieving my goal to display each repletion in a portal. What would fit my needs perfectly would be to have say 10 repetitions of each of the address fields in the Contacts table itself. The relationship key AddressTypeAbs would remain the same but relate to the address fields’ repetition number. It doesn’t seem possible to relate to a repetition number but I might just have missed something. Just thought to post this before giving up entirely. Any thoughts?
  8. OK. I started looking at Javascript libraries and some FMP integration but instead found a reasonable solution for anyone who's interested. Not sure if it works cross platform yet but it works on Mac. Can someone test? A free plugin called ExifPlugin parses the exif data (both platforms). I played around a bit with the included demo file and generated a layout and field structure that made more sense. Each exif display field has an auto enter calculation so that it can store the specific exif data on a 'folder' import. I realised this only works on images stored in the DB itself so a quick script to import a folder of images then re-import the image container again as reference only from the same folder. The first import is slow for 2 reasons. 1. It is storing the whole image so this will be many Mb's per image and 2. generating a thumbnail of the image in the FMP process is very slow. This could be taken out of the equation but I like the concept of a tiny image reference embedded in the DB, just in case. The second import simply updates the image container from actual file to reference only, and is quick. The best thing is that the DB is now 180Kb vs 20.5Mb for 4 images yet displays the same info. Of course the DB now always needs the images in the original folder to reference to. So I am now thinking that the script could perform without dialog if you were to think of the folder it selected the images from as a watch folder (hard coded) and a bit of extra shell script could then move the files to their final storage location and then update the file paths for the container reference. Could be a useful tool. So here it is. (Don't try this on a folder of 900 images unless you're willing to wait for a long time.) Special thanks go out to jensteich.de for the FREE plugin and to Comment for keeping my mind active. ExifPlugIn.zip
  9. OK Your guess makes sense. 1. I will have to delve into v13 to see what has changed/added to address this issue - GetContainerAttribute. 2. So Filemaker v11 is relying on an implementation of Mac OS and iPhoto to achieve this? What happens on Windows? I'm still scratching my head over the fact that the metadata is a part of the file header no matter where it is - CF, SD, HD. Its not just FMP that has this issue it seems. Thanks Comment. Always appreciate your input.
  10. This is not so much about wanting a solution but about wanting to know the technical reasons behind the inability to import exif data with an import of images from a hard drive. As a photographer the usual workflow is to dump the CF or SD card data to HD first so it can be sorted, assessed etc. FMP11 can import the image's exif metadata from a 'device' such as a camera, smartphone or card reader but not from an image directory on a hard drive. I would prefer to import after this workflow step is completed and not directly from the camera. I know there are solutions to this problem via plugins, Java script integration, RAMDisk creation and so on but I would just like to know what is the technical caveat that restricts FMP (and other programs) from importing exif from a hard drive?
  11. Back to it. I reverted from a set fields to a set global variables for this and it all works. So I must have missed something.
  12. By wacky I mean the result for start size and end seemed to work, from recollection, (verified by global variables being displayed on the layout via <<$$xxx>>) but the Set Selection result would be something completely different. Tried as script then global variables and did think about the expiration issue. New to Set Selection[]. I'm pretty sure I just missed something but I have had problems before using variables and have to had set some global fields instead. Again I probably just missed something then too. Thanks. I'm glad I posted.
  13. Thanks for the very fast response Comment, you beat me to it. I figured it out using Set Selection[] and a script trigger. For others I will try to explain. For the Format drop down it needed a script trigger on exiting the text field to set the ActiveSelectionStart & ActiveSelectionSize prior to clicking on the drop down field or the focus was already lost. For the Special Character popup window it did not need the above script trigger in play to work but it was there anyway so may as well use it. So for each problem there needs to be two scripts in the process, the second one in each being for the resultant selection and that's where to use the Set Selection[] script step to get you back to the text field and back to the original selection of text. Now two very curious things. 1. This didn't work with variables. The result was just wacky but it did work with fields and using the Set Field[] script step instead. 2. Generating the Start, Size and calculating the End (as calculation field) resulted in capturing one more character so I had to add a "-1" to the Set Selection[] script step. Could have done it in the calc instead. Didn't matter.
  14. This one has stumped me. I am writing a sort of html editor in FM 11. So I went ahead and built a set of buttons just like TinyMce and started to replicate their functionality. Using the Insert Calculated Result I can select a a section of text in a field and add the html tags at the beginning and the end of the selection depending on what button is clicked. To view the result I use a web viewer on another Tab. All good for most of it until I needed to leave the field to replicate the TinyMce functionality. 1. H1, H2, paragraph etc. come in a drop down menu in TinyMce. 2. Special characters come in a window popup in TinyMce as they are too numerous to display. So my problem is that I have to leave the selection I have made in the text field in order to display the drop down selection or the popup window and then the script no longer has a reference for where to Insert its Calculated result. The following example script steps work well when individual buttons are on the same layout. Set Variable [ $start; Value:Get ( ActiveSelectionStart ) ] Set Variable [ $size; Value:Get ( ActiveSelectionSize ) ] Set Variable [ $content; Value:Middle ( Topics::Topic_Text_body; $start ; $size ) ] Insert Calculated Result [ Let ([ $sp = Get(ScriptParameter) ; $newContent = "<" & Get(ScriptParameter) & ">" & $content &"</" & Get(ScriptParameter) & ">" ] ; Case ( $sp = "B" ; TextStyleAdd ( $newContent ; Bold ) ; $sp = "I" ; TextStyleAdd ( $newContent ; Italic ) ; $sp = "u" ; TextStyleAdd ( $newContent ; Underline ) ; $sp = "st" ; TextStyleAdd ( $Content ; Strikethrough ) ; $sp = "sub" ; TextStyleAdd ( $newContent ; Subscript ) ; $sp = "sup" ; TextStyleAdd ( $newContent ; Superscript ) ; $newContent ) ) ] The case function is there just to generate a bit of visual feedback on the edit Tab text field. If I set the variables prior to leaving the field is there any way to go back to the text originally selected in that field or the position in that field (in the case of inserting special characters) before Inserting the Calculated Result?
  15. Sorry for the delayed response. Settings wrong in my profile so I didn't know there was any response. I did get this working via a script trigger from memory. I will re-address it when I can because I am still not convinced this is the right approach. In response to your question. The person is related to the company and visa versa. This can be via a record ID or a contact ID or whatever. I am working on making this more idiot-proof but in the past I have setup the relationship via a contact ID field. For various reasons I am not doing that any more. I agree with your concern. The problem is that if a Company (contact) changes name or a person changes companies then the relationship is broken. Same problem with any other related table - notes etc. In the past my solution has been via a laborious work method of first changing the personnel record/s relationship field then going back to the company record and changing the relationship field there to match. Not at all elegant. The other way of course is to just keep data entering and end up with duplicate records. Again not elegant. I don't have a good answer to this yet but I'm working on it (and 100's of other things). I will post my findings if I ever get to fully idiot-proof this.
  • Create New...

Important Information
