How to extract info from portal rows

[The Learner]

Hi,

I have a table called Books and a table called Publication.

On a layout based on Books, I have a portal based on boo_PUB_BooksId (Books::Id = boo_PUB_BooksId), where one or two publication dates are selected.

In a card window based on Books, I have a portal based on boo_BOOKS::Editor (Books::g_SelectedEditor = boo_BOOKS::Editor) that displays the nos and the titles of books from selected editors. That works fine. Except I want to add the publication date or dates in that portal. But I can’t manage to extract the dates from boo_PUB_BooksId (the portal on the Books layout). Could someone help me achieve that.

I’d like the dates to be displayed as follows:

If single date: 25 October 2023

If two dates: 25 October 2023 and 30 October 2023

Right now, the card window is displayed by means of a script and a global field that is populated from a value list.

Thanks for your help.

Edited November 26, 2023 by The Learner

[bcooney]

You want to add the publication dates to the editor’s portal or show the dates above the portal? Is the publication date related at all to the editor?

if you want to show the dates, either show both portals in the card or add a calc (several ways) that displays a list of publication dates above the editors portal. 

[The Learner]

Thank you for your response.

The publication dates are already displayed in the portal to the publication table on the main layout. l want these dates to also be displayed in the editor's portal in the card window. I don't want the publication dates above the portal, simply in each row of the portal, beside the book no and the book title.

 

[comment]

I am afraid your question is still not quite clear.

Assuming that one book can have many publications and that each publication has a single publication date, you can use a calculation field defined in the Books table as (result is Text) =

Substitute ( List ( Publications::PublicationDate ) ; ¶ ; ", " )

to display all related publications' dates as a comma separated list, with the dates formatted in your file's stored date format. 

If you want to format the dates in a custom format and/or use the word "and" instead of the last comma, then more work will be required.

 

[The Learner]

Thanks very much. Your calculation does what I want except for the comma instead of the and. But I'll live with it.

[comment]

2 minutes ago, The Learner said:

except for the comma instead of the and

Easy to fix:
https://fmforums.com/topic/107428-replace-the-last-with-and/?do=findComment&comment=484516

 

[The Learner]

Issue resolved. Thank you.

[bcooney]

That’s exactly what I suggested (“add a calc (several ways)” and you said it’s not what you needed. You want the dates in the editor’s portal. That made me question your data model. 

[comment]

Just a small point of clarification:

Your title says "extract info from portal rows". That's not what my suggestion does. A portal is a layout object. My calculation works at the data level. It does not rely on a portal and will work regardless of whether there is one on the current layout or not.

OTOH, if you do have a portal, and the portal is filtered, the suggested calculation will ignore the filtering and you will see a discrepancy between the portal and the calculated result.

 

[The Learner]

4 hours ago, The Learner said:

l want these dates to also be displayed in the editor's portal in the card window

Well, I'm sorry if there was some misunderstanding. But above is what I said in my second post.

[bcooney]

Yes, exactly. Comment provided a way to show a list of dates. They are not in the editor’s portal, are they?

[Søren Dyhr]

On 11/26/2023 at 9:04 PM, comment said:

OTOH, if you do have a portal, and the portal is filtered, the suggested calculation will ignore the filtering and you will see a discrepancy between the portal and the calculated result.

But instead of the calculated list, you could use the summary List Of living on the child side of the relation, and showing the summary field via yet another one line portal where the applied filtering is the exact same - the substitutions of the pilcrows is then made by massaging with

GetLayoutObjectAttribute ( objectName ; attributeName {; repetitionNumber ; portalRowNumber} )

....after having named the field shown in the new portal as object. Resizing this extra portal to 1 pix times 1 pix might be in order, and stowing it away in a corner seems obvious!

 

--sd

[comment]

3 hours ago, Søren Dyhr said:

Resizing this extra portal to 1 pix times 1 pix might be in order, and stowing it away in a corner seems obvious!

That's not necessary. You could simply put the portal on the margin of the layout, outside of any layout part.

However, all this added complexity is not required here, since the original portal is not filtered.

 

[Søren Dyhr]

6 hours ago, comment said:

However, all this added complexity is not required here, since the original portal is not filtered.

Agreed....