Calc combined list of two fields

[shredded]

Hi,

 

I am trying to pull data from a portal, for example I have Standard and Type fields.

I would like to pull that portal information into a text field that would show:

STANDARDFIELD & ": " & TYPEFIELD

STANDARDFIELD & ": " & TYPEFIELD

STANDARDFIELD & ": " & TYPEFIELD

ASTM: 12345

ASTM: 34556

ASTM: 85858

or

ASTM: 12345, ASTM: 34556, ASTM: 85858

 

Any help would be greatttt! Thanks

 

 

 

[comment]

What would be the purpose of that?

[shredded]

The portal is for data entry, the output to a text field would be for a detailed description with other information that can be viewed on another layout. 

[comment]

Why can't you simply use a portal to view the same information on the other layout? To do what you ask for is not simple.

[comment]

Quote

or

ASTM: 12345, ASTM: 34556, ASTM: 85858

The simplest way would probably be to define a calculation field in the child table =

STANDARDFIELD & ": " & TYPEFIELD 

and another calculation field in the parent table =

Substitute ( List ( Child::cCalcField ; ¶ ; ", " )

Result type should be Text in both.

 

Another option is to use the ExecuteSQL() function to get the two fields, then use Substitute to replace both the field and the record separators.

 

Or use a recursive custom function such as: http://www.briandunning.com/cf/309

 

Edited September 8, 2016 by comment

[shredded]

I have a similar calc that pulls data from 2 tables, but in this situation its both from the same table. I tried just changing the values, but I am pretty noob on SQL.

 

ExecuteSQL ( "   SELECT \"Standard\", M.\"Type\"   FROM Standards C   JOIN \"Standards\" M ON     M.\"__StandardID\" = C.\"__ComplianceID\"   WHERE \"_fkParentID\" = ?   ORDER BY \"Value\" DESC   " ; "% " ; ", " ; __ChassisID )

[comment]

I am at a disadvantage here, because (a) I don't know your field and table names, and (b) I have no way to test this. See if you can use this as your starting point:

ExecuteSQL( "SELECT Standard, Type FROM Standards WHERE fkParentID = ?" ; "-"; "," ; pkParentID )

 

[shredded]

comment, 

Can you check this demo file? I think I blew it and forgot to mention there was a join table.

THANKS!!!!!!

Test.fmp12

[comment]

Sorry, I cannot open .fmp12 files.

[shredded]

Heres a screenshot of my relationships.

[comment]

See if this works for you (untested):

ExecuteSQL ( "SELECT Standard, Type ¶
FROM Certifications INNER JOIN Standards  ¶ 
ON Certifications.\"_fkStandardsID\" = Standards.\"__StandardsID\" ¶
WHERE Certifications.\"_fkParentID\" = ?";
 "-"; "," ; 
__ParentID )

 

Edited September 9, 2016 by comment

[shredded]

Yes this works If I add "/ to the values. Thank you so much! 

Can you explain how I could add dash space at beginning and enter at end?

- ASTM: 123

- ASTM: 456

[comment]

21 minutes ago, shredded said:

Can you explain how I could add dash space at beginning and enter at end?

I think you will learn how to do that by reading the help on the ExecuteSQL() function.