Jonas Löfvenmark

Thanks for your input, it is much appreciated! :laugh2:

Dear bcooney, thank you for your input! There are different files as this project will be a huge PIM system involving thousands of products from hundreds of manufacturers sold through multiple franchises in different regions and languages with storing of images as well as price calculations in different currencies, also tracking competition pricing. Phew, almost fainted writing that - what a project... Anyway, 1 file simply gets too blurry incorporating everything so I try to plan ahead and keep things as separate as possible. Do items related to more than one Company? Do items have more than one region? Yes and yes...

I have a table of Companies, operating in differing Regions (defined in a Regions table), stored in a repeating Regions field for each company. For my Items table (located in a different file) would like to store Companies in a repeating field in a similar matter, and be able to set Regions for each item via a value list based on the Regions defined in each Company. If: Company A have Regions 1, 2, 3 Company B have Regions 2, 4 and I select Company A for the first repetition of the Items Company field and B for the second repetition, I would like the value list for the items Region first repetition to show 1, 2, 3, and the second repetition to show 2,4. i.e an item might belong to a company, but not all the regions of that company. It would be nice if the Regions Value list only showed the Regions corresponding to each company. My first approach was setting up a value list of Regions related to each Company in the Company File, and then creating a relation to the (linked) company DB in my Items file, and using the external value list method but the value list only shows "unrelated". I tried using portals, but.... I'm getting over my head here! best regards Jonas

the goal is transforming something like this: id1: "Audiogear -> Microphones -> USB-Microphones" id2: "Audiogear -> Wireless equipment -> In Ear-monitoring -> Receivers" id3: "Audiogear -> Wireless equipment -> In Ear-monitoring -> Senders" id4: "Guitars -> Instruments -> Electric Guitars" etc. for 485 records times 4 languages to: id1: "Audiogear", parent: root id2: "Microphones", parent: 1 id3: "USB-Microphones", parent: 2 id4: "Wireless equipment", parent: 1 id5: "In Ear-monitoring", parent: 4 id6: "Receivers", parent: 5 id7: "Senders", parent: 5 id8: "Guitars", parent: root id9: "Instruments", parent: 8 id10: "Electric Guitars", parent: 9 By dividing the category string into 4 sets of calculated fields (one for name, one for id) we get the different names and ids for each level. For the first record: level1id: 1 level1name: "Audiogear" level2id: 2 level2name: "Microphones" level3id: 3 level3name: "USB-Microphones" level4id: null level4name: null Then for each following post we need to check if each level name has changed from the previous post using getnthrecord. If so assign a new id, else use the previous records levelid and levelname. So far fine enough. But when we want to add an (reversed) ordering parameter to the result, like: id1: "Audiogear", parent: root, order: 2 ... id8: "Guitars", parent: root, order: 1 We need to find out how many unique items there are for each level - from the bottom and up. Using getnthrecord on level2, 3 and 4 works fine as they change a lot. But level1 started showing "?" half way through. Anyway - using valuelists all is good and performance is top notch! For the fun of it I have included the file for reference ;) categories.fp7.zip

Interesting - I get the same result on my client using mac, PC, and Filemaker Server windows with loads of memory! I need this for exporting data to a webiste where items are sorted based on priority (higher values first) where as the data in filemaker is sorted ascending. Also the filemaker data has one record for each item and group where as the website needs groups separated from items with parent linking to group items. Filemaker data 1 Computers -> Mac 2 Computers -> PC Webiste data 1 Computers 2 Mac, parent 1 3 PC, parent 1 (yes, it is poorly structured filemaker data to begin with....) Anyway - using getnthrecord on 400 category items with up to 4 levels and 4 languages was running kind of slow. With value lists is is warp-speed Thanks for pointing out, valuelistitem function makes them unique though. And by relating each level of the FM category string to a new instance of the table i get the child categories items only.

Funny how sometimes you just need to sit down and formulate a problem to understand the solution Found what I believe to be the best solution by avoiding getnthrecord and instead using a value list. By defining a value list for the Name column and using the valuelistfunction you get a set of unique set of names. Then find the position of the record's name in the list of names, like: Let([uqNames= ¶&ValueListItems("myfile";"myvaluelist")&¶ ; myname = name ; mylist = Right(uqNames;Length(uqNames)-Position(uqNames;¶&myname&¶;0;1) )] ; PatternCount ( mylist ; ¶ ) )

I am trying to define a calculation to check if the name field has changed from the next record, and add a counter if so like: name order DESC A 3 A 3 B 2 C 1 Case(Get(Postnumber)=Get(FoundPosts);1;Let([ this=name; next=GetNthRecord(name;Get(Postnumber)+1); value=GetNthRecord(order DESC;Get(Postnumber)+1)]; Case(Exact(this;next);value;value+1))) (I've translated the get functions from swedish to english as I remember them - if wrong it's my bad) The calculation above functions fine up till 173 records. Adding record 174 the calculation starts displaying "?". Sample file attached. I have tried declaring variables as above, not declaring variables, defining as a function without any luck. Any tips on how to move forward is greatly welcome. Is there another way to accomplish the same result? Have never used statistics fields, but the name field will eventually be a calculation itself - have have just dumbed it down to understand what is going on. Thanks! Jonas. test.zip

Yes, that could be usefull! Thanks for input!! Position( ¶ & List( cities::name ) & ¶ ; ¶ & "Paris" & ¶ ;0 ;1 )

Thanks! Will look into that. For now I came up with: getnthrecord( cities::id ; PatternCount(Left( List( cities::name) ; Position( List( cities::name ) ; "Paris" ;0 ;1 )); ¶) +1)

I see.... hm. Thought I had id! Kudos to your hawk eyes. Any idea on how to do this? basically an automation of what happends when I manually select a entry in a valuelist that is set to only show the second column.

Found it! Thanks for your fast reply though. GetNthRecord ( cities::id ; Position(List(cities::name) ; "Paris" ;0 ;1 ))

Good day dear FM friends, I have hot stuck on something much trivial - something I though would be a breeze to set up, but apparently not. Please help, dear friends! I thought I could easily find a function similar to GetValue but the other way around. With a valuelist based on cities::id to show cities::name I would need to do something like: getValueListItem("cities value list"; "Paris") to return cities::id. I understand I can use position to find the position in the list, but how about the corresponding id?

Thank you all for all your input! I have this working for one hosted file, but strangely enough not for the other; by setting the filemaker service to "allow desktop access" I can export to a server side local drive by using the CNSImage_Export function on a variable. Exactly what I wanted! Why this only works for one of the files is a mystery. Both file's script run fine from the client, but only one of them from the server. Both files have the same user with full access, and both scripts are run with full user access. Next I will look into importing images from a network share, and I found out by trail and error that it does not work (@Wim Decorte: thanks for confirming), and will have to look at the ScriptMaster thread (@bcooney: thanks for the tip)! I did try to set up a different user with full access for the FM service, but then I could not log on to the filemaker server. Any input on that is greatly appreciated.