SID-01158: How to combine data from two forms in one search

Status:	Answered	TWiki version:	5.0.1	Perl version:	5.10.0
Category:	CategorySearch	Server OS:	Debian 5.0.8, kernel 2.6.26	Last update:	14 years ago

Hi! I'm trying to embed a QuerySearch inside a FormattedSearch, but got one problem.

The scenario: I have two pages with two different forms attached. One has information about an item, the other information about the owner (member). The two forms have a common ID number field to make it possible to connect one item to one owner.
I use a search to list all the item pages in a table but I also want to include some information about the owner - on the same table row - so I added a QuerySearch to get the owner name. The idea is to use the ItemIdNr from the first search and do a lookup with this ID using the embedded QuerySearch.

It is almost working (I think), I can see the variable expansion is working (the ID number is correct) but I get a syntax error:

<form action="%SCRIPTURLPATH%/view%SCRIPTSUFFIX%/%WEB%/%TOPIC%"> 
<input type="text" name="qLghNr" value="%URLPARAM{"qIdNr"}%" size="2" />
| *ID number* | *Owner Name* | *Property* |
%SEARCH{ search="META:FIELD.*?ItemIdNr.*?%URLPARAM{"qIdNr"}%" topic="ItemInfoPage*" nosearch="on" nototal="on" regex="on" noheader="on" format="| $formfield(ItemIdNr) | %SEARCH{"fields[name='FormType'].value='OwnerDb' AND fields[name='ItemIdNr'].value=$formfield(ItemIdNr)" type="query" nosearch="on" nototal="on" format="$formfield(OwnerName)"}% | $formfield(ItemProperty) |" }%
</form>

Could not perform search. Error was: Syntax error in 'fields[name='FormType'].value='OwnerDb' AND fields[name='ItemIdNr'].value=15' at '15'

When I add ' ' around the value in

fields[name='ItemIdNr'].value='$formfield(ItemIdNr)'

If I change the inner QuerySearch from

fields[name='ItemIdNr'].value=$formfield(ItemIdNr)

fields[name='ItemIdNr'].value=15

What am I doing wrong??

-- AlexanderAhman - 2011-05-01

Discussion and Answer

You need to escape the SEARCH inside the format="", otherwise it executes just once, before the outer search. FormattedSearch has the details on escaping. Keep in mind that nested searches can be expensive, aka slow.

You can also use a $query() inside the format="". FormattedSearch has more.

Why not simply use VarFORMFIELD in your SEARCH format to pull form fields from the other forms? You need to escape the variable as well, such as $percntFORMFIELD{ "OwnerName" topic="..." }$percnt.

-- PeterThoeny - 2011-05-02

Thank you for your answer!

To be able to use VarFORMFIELD or $query(), you need to know the topic name to get the data from (correct?). Problem is I don't.
The two pages I was talking about should really be two types of pages. The number of pages can be many.

I thought I tried all types of escaping there is, but obviously not the correct one. But your answer lead to the right place, you can find a working recipe below.

Summary: It will search for topics with their names beginning with ItemInfoPage and for each search hit, A: get the ID number (ItemIdNr) from that topic, B: use that ID number to do a second search for topics with a certain form attached and with a ID number equal to the one found in A, C: use the second search hit to get needed data from that topic.

Hmm, that was a bit difficult to describe, I hope you understood what I meant despite my english...

<form action="%SCRIPTURLPATH%/view%SCRIPTSUFFIX%/%WEB%/%TOPIC%">
<input type="text" name="qLghNr" value="%URLPARAM{"qIdNr"}%" size="2" />
| *ID number* | *Owner Name* | *Property* |
%SEARCH{ search="META:FIELD.*?ItemIdNr.*?%URLPARAM{"qIdNr"}%" topic="ItemInfoPage*" nosearch="on" nototal="on" regex="on" noheader="on" format="| $formfield(ItemIdNr) | $percntSEARCH{\"fields[name='FormType'].value='OwnerDb' AND fields[name='ItemIdNr'].value='$formfield(ItemIdNr)'\" type=\"query\" nosearch=\"on\" nototal=\"on\" format=\"$dollarformfield(OwnerName)\"}$percnt | $formfield(ItemProperty) |" }%
</form>

-- AlexanderAhman - 2011-05-02

Thanks for sharing your solution with the TWiki community!

-- PeterThoeny - 2011-05-02

If you answer a question - or someone answered one of your questions - please remember to edit the page and set the status to answered. The status selector is below the edit box.