Convert field in portal to list

[Jason Goldsmith]

I have a related field that displays in multiple rows in a portal. I need to create a list in sentence form using the related field as it presents sequentially in the portal (Field occurance 1, Field occurance 2, Field occurance 3, ...)

Would I best do this with a script that copies each portal row and pastes the data into global fields, and then concatonates the globals in a calculation? Is there a more elegant way to do this?

Thanks

[Genx]

That's a bit weird, if you can avoid it try your best - that being said a global wouldn't really work because the only way to non alphabetically organise a list is to use a custom values.

[Søren Dyhr]

I'll second that, it's a wrong strategy, when all it takes is a GTRR(SO) to a layout in listview containing this string only:

Field occurence @@ <>

...no real fields shown at all. But if you need this to go beyond eyeballing, such as copy and paste it into a wordprocessor document, are there two choises.

Either grap this via the preview and a "copy" - which makes an image to paste.

....Or if it's going to recieve the word processing styles chosen in the document, make following UNSTORED calc'field in the child table, as the only field shown in the layout:

"Field occurance "& Get ( RecordNumber ) & ": " & theData

...and your script will go like this:

GTTR(SO)

Copy All Records

--sd

[Jason Goldsmith]

Here's an example of what I'm trying to accomplish. The goal is to create a simple permanent list of items in a portal. Thanks for your help

Portal.fp7.zip

Edited June 18, 2006 by Guest

[Søren Dyhr]

Well it's pretty easy, but why is the denomalization required?

Set Variable [ $where; Value:Count ( Friut::ID ) ] 

Set Variable [ $collection; Value:Case(0;0) ] 

Loop 

      Set Variable [ $collection; Value:GetNthRecord ( Friut::Fruit ; $where ) & "¶" & $collection ] 

      Set Variable [ $where; Value:$where-1 ] 

      Exit Loop If [ $where=0 ] 

End Loop 

Set Field [ Friut::List; $collection ] 

...but it's a weird request as Genx said, conflicting conventional wisdom, so please elaborate ...it looks like a search for who did we have we sold Kiwis or Oranges to lately.

--sd

[Tricky]

Can you explain what the objective is of this action. From the user point of view. Why do you want this? I do not understand the usefulness of it now...

[Genx]

... Assuming you actually want a dodgey type value list... you know what you could do? Wait know i've lost it. I was thinking something along the lines of using a 2 values value list sorting by the second value which is your portal row number but i don't remember if Get(PortalRowNumber) actually returns the number of the portal row...

[Tricky]

I looked at your file: do users have to be able to change this list? If not, why not use a conditional value list, and show that in a field? Or is the order also important? Cause the list would be automatically sorted.

[Tricky]

You were just ahead of me... thought along the same lines but did not thing about the portal number. That seems like a good idea...?

Nah, just tried, will not work. portalnumber returns 0 everywhere.

I am still interested in hearing what the objective is.

[LaRetta]

You mean like this?

If you take advantage of another field, like a unique ID which you SHOULD have anyway, it is easy to keep the value list in the sequence of creation order.

PortalREV.zip

[Raybaudi]

Hi

I think that you can use a custom function for this.

Give a look to the custom inside the file in this topic.

Portal.zip

[Razumovsky]

All you need to do is identify the sort order. In your example, it looks like you want to have the list sorted by the same parameter as the portal. As you did not specify any sort for the relationship or portal, the default is creation order. So, you need to create an auto enter or unstored calc field that will allow you to sort by that (creation timestamp, serial number, get(recordID)...) and include that as a second field in the value list, with sort by second field selected.

Another approach is to compile the list using a custom function and get nth record.

I do have to echo the questions on why this is needed though. If it is just for display, you need nothing more than the portal.

-Raz

*need to speed up my typing skills...LaRetta and Daniele got it down.

Edited June 18, 2006 by Guest

[Genx]

Good thinking guys, we've put together in some convoluted way something that probably has no real use .

[Jason Goldsmith]

Sorry, I stepped away from my computer and am surprised (and happy) to see so many responses.

The objective is the following. I'm building a simple electronic medical record. Separate tables exist for Visits, Diagnoses, Medications, Allergies, etc. From a visit in the Visit table, I can look at the Diagnoses table through a portal and see all the past diagnoses. Likewise, I can look at the Medications, Allergies, etc. The key field is, of course, the medical record number. Thus, I'm looking at the diagnoses, meds, allergies for a given patient.

A medical record, however, requires that for each visit, a permanent record (and not a portal view) be created. So I need to covert the items in a portal to a line of text. The advantages of a line of text including printing: "The patient's allergies are ..."

I hope that make sense.

Thanks,

Jason

[LaRetta]

I disagree, Genx. Jason had the need. And it is NOT convoluted. Just because you don't KNOW the use, doesn't mean there isn't one.

Wanting a list string of related records in creation order has been a request *I've* received before. Don't close your mind to possibilities when something is presented just because you've never had reason to use it directly yourself.

UPDATE: Do not for ONE moment think that knowing you can switch what fields are in the value list - and sort by any other field - is convoluted - it can be used in other situations as well.

Edited June 18, 2006 by Guest

[Søren Dyhr]

I would say that the Copy All records utilization in my first suggestion solves this. There is absolutely no need to stuff an extra field for that.

--sd

[LaRetta]

So let's pollute the clipboard instead which, BTW, produces a multiline which will THEN need to be converted.

UPDATE: People need to remember that, when solving ONE problem here, we should take the techniques and BROADEN our thinking about how they can be used. A technique is neither good or bad - if it works. And it can go into our bag of tricks to be used in other situations.

Edited June 18, 2006 by Guest

[Genx]

Gosh LaRetta i was just having some fun, i'm watching the soccer and it's just sort of slow. And you don't have to worry, i'm not thinking about anything for even one moment at... the moment :)

[LaRetta]

No prob, Genx.

BTW, I 'probably' wouldn't create a field for this either. I would use it in a script to set a global text whenever I needed it.

[Jason Goldsmith]

Thanks for your help. As a beginner, it's going to take some time for me to try out these suggestions. I'll likely be back with more questions in the next day or so.

[Søren Dyhr]

So let's pollute the clipboard instead which, BTW, produces a multiline which will THEN need to be converted.

It won't be if it's tranported to a wordprocessor anyway! Or do you know of other x-platformish ways to sneak snippets into your wordprocessor??

--sd

[LaRetta]

I see no mention of a word processor or a need to place the contents on the clipboard and possibly wipe out a User's prior copy. It simply isn't necessary here. I used a calculation to display the results as ONE idea only.

If the medical record is form view (which I assume it is) AND if one medical record is printed, I *might* use a global merge and plant the data before each print. Or a calculation as merge can fit right into the letter as well.

We simply don't know enough about the PROCESS (as Tricky pointed out) to know the best solution. Heck, the sample file didn't even HAVE clients and the IDs were incorrect! Speculation is speculation. But I don't use clipboard unless I script capturing User prior clipboard and replacing it at end of script. NOTHING upsets a User more than having surprises on their clipboard because we Developers poorly plan and use it ourselves. :wink2:

[Søren Dyhr]

Speculation is speculation

Yes, and this is exactly why we find the request weird ...but way too commonly occuring - A request ignoring the oppertunity to fill us in on the mission.

But I don't use clipboard unless I script capturing User prior clipboard and replacing it at end of script.

As if I never have evangelized this before, and actually have attempted to give this a solution if the matter should be ported.

Neither would I be caugth redhanded in using globals unless other opportunities have been exhausted ...there is nothing that excuses breaking normalized structures down just for printout, when a layout with a listview could be designed to make the form required.

To me looks the question like a straight forward reporting task, which in my humble opinion belongs to the child file of a related structure, if the tunneling of the parent records values into report is the problem ...is it this particular lack of knowledge we need to address, isn't it???

--sd

[Razumovsky]

Actually, this seems to be a straightforward lookup task along the lines of customer info on an invoice (capture related data at a specific point in time that may change later), not reporting at all. Would you consider storing customer address as a lookup on an invoice also breaking normalization?

[Tricky]

Gosh, big discussion here. I agree with the statement that solutions that work, are good solutions. There are always alternatives to doing it. If we all knew everything, we would not be human....

JASON: what I am wondering about is why the related items that are created in your situation are not important enough, so to speak. But I agree with others that it might be similar to an invoice situation. I work for a big company and the tool I am responsible for, calculates sales to customer prices for tendering. As soon as the price is set and the customer gives the job to us, we also have to freeze that exact situation. So, that is probably it.

Quite apart from the somewhat touchy replies, I find this list (to which I am new) very stimulating, also because of the attached examples. I like that, so hurray for all with an open mind to alternatives. Or, to use Edvard Debono's principle: think outside the box!

[LaRetta]

...in using globals unless other opportunities have been exhausted

Ah. But a FIELD is required to VIEW anything in FileMaker, agreed? And globals can be accessed anywhere and placed even on unrelated tables. They can be SET with data from anywhere as well. They are a Developer's strongest asset - yes, even over variables because of the very reasons I'm stating. I use script globals over and over. I set relationships (variables can't do that) and I use them to display various script messages to Users. I don't apologize for using globals (in the least).

The point is this ... a VALUE LIST takes little/no resources. And a value list can set global or standard field; can be copied to clipboard; can be used in variables or calcs and the list goes on. I suggested a value list solution. How it is used is open to our imaginations. How it needs to be used in THIS situation, only Jason can truly know - whether to view only or plant the data. Many people think a value list can only be produced in ALPHA ORDER and it is NOT true. That's the only reason I posted.

Great discussion everyone! Thank you! :wink2:

[Søren Dyhr]

And a value list can set global or standard field; can be copied to clipboard; can be used in variables or calcs and the list goes on. I suggested a value list solution.

But it's the moving stuff around approaching somewhere near sync that worries me. I'm willing to accept unstored calc'fields as approach but not the idea of having the some data stored twice - say in a global field ...because you can say to your self, when was this snapshot taken? With unstored calcfields is it a little more predictable. It has to be exactly when the layout is rendered - which by the way is the same way an ordinary report behaves ...so why not stick to straight forward reporting where we then apply the styling required?

If you say, the user can not copy and paste from it ...are we way into the assumptions regarding the mission!

--sd

[Søren Dyhr]

Would you consider storing customer address as a lookup on an invoice also breaking normalization?

Good question! If it's the shipping address is it a historic fact at the time the invoice were issued and it should be a lookup, but the clients name is another matter...

--sd

[LaRetta]

I usually agreen with you, Soren but ...

...but the clients name is another matter...

We must plant everything into our invoices. What happens if the customer changes their name (or it was incorrectly created the first time) and there is a legal question about ownership or a mis-shipment? This is especially true in the legal profession but usually affects most businesses. I even write the product description because, if down the road the Owner changes the name, we have documentation of the product name as well. What goes out that door must indeed be a snapshot for many businesses.

Taking snapshots of data (for whatever reason) is part of what we do - which is why we have so many ways of accomplishing it (pdf, excel, clipboard, export and so on). But we don't really know the need here so it is all moot. :wink2:

[Søren Dyhr]

You're right LaRetta, but perhaps ought the invoice date and the clientID bring in the correct Name, address etc. where these are kept a related table as a log... to prevent a lot of redundance. It not entirely legal, but more a developers dream of an agile vehicle to take for a spin, think of 50 invoices to the same company means the storage of 50 redundant addresses, where some keying could make these one or perhaps 3 related records value ushered in for the most nomadic companies in our portfolio.

--sd

[Squeaky Data]

I have been using a recursive custom function to collect text from related fields using GetNthRecord.

If you have FileMaker 8 Advanced you can check out the custom function in the attached file.

SumTextTest.fp7_2.zip

[LaRetta]

I guess my question is ... why use a custom function; particularly utilizing GetNthRecord() which will become slower and slower as file size increases when a simple value list will do the trick? Maybe I'm missing something here but if so, I don't see it.

GetNthRecord is being used everywhere with little consideration of its speed problems. This is fine in some circumstances but should be carefully considered.

nth Record

LaRetta

[comment]

Value lists are not exactly speed animals either. The thread you linked to is concerned with a CASCADING calculation. I see no reason why using GetNthRecord () over a relationship from Invoices to LineItems should get progressively slower as the number of Invoices increases. It needs to deal with that specific Invoice's items only. And there's the advantage of not having to create an artificial value list just for the purpose of the calculation.

[Squeaky Data]

I have not seen any perceptable speed hit with the custom function and GetNthRecord but I am only dealing with 15,000 parent records and 60,000 related child records so I will keep monitoring things as the dataset grows.

In your value list example using a value list that sorts on a second field any values missing data in the second field will not appear in the calculated result.

[Andy Reimer]

I've just had need for a similar function.

I have a portal on an invoice layout that shows the items related to that invoice. I was able to create a list of the item names with Soren's script and put that list in the body of an email sent to the client.

No doubt there are several ways to do it, but this seems the simplest.