Display related records for multiple queries in portal

[jamesHP]

We have a table of job reports which displays the following fields:

1. Job number (unique number)
2. Job Title (text which is autofilled using the job number)

Portal results showing a list of work done: 

3. Work Done (number)
4. Hours (number)
5. Cost (number - based on an fixed hourly rate multiplied by Hours)

Beneath the portal it then shows a total field:

6. Hours Total (Total of all hours displayed in the portal)
7. Total Cost (Hours Total multiplied by a fixed hourly fee)

---------------

I want to create a new table with a portal search that I can type in multiple job numbers in a search field and it will display a list from the table above in the following format:

1. Job Number, 2. Job Title, 6. Hours Total

Then I will create a couple of fields at the bottom which will add all the hours and give a total cost.

The problem is I cannot work out how, or if it is even possible to search for multiple job numbers in a field to display the relevant results in a portal. Is it possible or do I need to go about it in a different way?

[eos]

Then I will create a couple of fields at the bottom which will add all the hours and give a total cost.Create these fieldsin the Jobreport table

​Let me recommend that you don't create calc fields in your “other table”, but instead create these fields as summary fields directly in the JobReport table; these “native” fields can then be used in reports, as well as in a portal – or any relationships, for that matter.

To display a quasi-find result in a portal, you could

• filter the portal – type your job numbers into the global field, as a list, and/or (for example) delimited by commas, then use 

Let (
  filterNumbers = Substitute ( YourTable::gPortalFilter ; [ " ," ; ¶ ] ; [ "," ; ¶ ] ) ; // clean/convert input into CR-delimited list
  not IsEmpty ( FilterValues ( Job::number ; filterNumbers ) )
)

Be aware that when using summary fields with filtered portals, you will need another (usually just one row tall) instance of this filtered portal to display the summary fields in, so that they display the correct values for the filtered set (rather than the entire related set).

• perform a SQL query and write its results into a global field that drives a dedicated relationship into the Job table

• do the same with native FM tools in an invisible window, where you compile the IDs via List of” summary, then close the window and set your match field.

Edited May 6, 2015 by eos

[jamesHP]

Brillaint thanks