Extracting fields from Table A to use in Table B

I have a table of values, like this:

Name	Position
Bob	Developer
Sally	Manager
Fred	Tester
Sue	Developer

I want to be able to use these values in cells of a different table, possibly on a different page.

Specific use case: a co-worker wants to pull from a table like this and insert the data into another table, e.g.

Name	Hired on	City	Position
Bob	July 1	San Francisco	Developer
...	...	...	...

Note that rows match 1:1 but field order may change.

My initial thoughts

• It seems variables would be a reasonable way t move the data around
• it seems like it would make sense to use incremented variable names, e.g. Name_1, Position_1, Name_2, Position_2, etc... where the row # is encoded into the variable name (that's how I'd do it in Perl or Shell...)
• I can use CALC to get the data and store it in variables.
• I would like to avoid manually typing (or pasting / editing) variable setting commands, one for each row of the table.
• I don't know how many rows there will ultimately be.

A loop construct springs to mind as a solution. I tried ForEach Plugin.

%FOR{"row" start="2" stop="%CALC{$ROW()}%" step="1" }%
   * Set Name_$row = $percntCALC{$T(R$row:C1)}$percnt
%NEXT{"row"}%

with no result (literaly; the CALC does not run)

If there's another plugin that would do the trick, that would be handy to know.

If there's a "better way" to do what I want to do, ideas are welcome.

(I've experimented in TWiki so far because that's what we're still running at Work. I would try to reproduce this in Foswiki but neither foswiki.org nor our beta installation at Work has ForEach installed).

---

Hello Vicki,

I've been writing dozens of FAQs to help our growing team at work, here on foswiki.org.

Regarding ForEachPlugin and CALC - both of these are "naughty" in that they register their syntax in a standard "non-standard" way (i.e. commonTagsHandler rather than registerTagHandler). Given the problems they solve, though, registerTagHandler isn't quite expressive enough, so they are forced to use commonTags API.

Anyway, it basically means that they don't participate in the normal inside-out, left-to-right expansion behaviour, so I'm afraid it's like shooting in the dark trying to get any combination of such macros to work properly.

I used to use ForEachPlugin too, I wrote Support.Faq39 to help us move away from ForEachPlugin. Perhaps you would have better luck using CALC with VarFORMAT.

Now, as to other solutions - you should know that there is FormQueryPlugin which allows you to query/re-format TML tables, however, I never had the patience to use it in anger. CrawfordCurrie is the author, I'm sure there's a few Support topics lying around that could serve as examples.

Personally, if I want data to be extractable/reportable, I always turn everything into topics. FlexFormPlugin, JQGridPlugin gives a nice experience for this style, https://wiki.trin.org.au/Mangroves/Bibliography/ is a publicly viewable demonstration of working with DataForms topics as tables.

Is there a reason you can't add more fields to your user topics? Or if you'd rather keep the employment data separate, generate a batch of topics in some other web which just links to their user topic?

Something like an example I've set up in Support/Question922/

Another option (if DataForms are too rigid), we developed SemanticLinksPlugin to create ad-hoc (relationship type) metadata, you could Eg. have [[HasMember::Main.BobDoe]] on the Developer topic, from which you could get a list of all members using a VarQUERY that looks like

%QUERY{'Developer'/slvalues[property='HasMember'].value}%

, or you could place these assertions on the user topics, Eg. [[HasRole::Roles.Developer]]

None of these ideas are particularly compelling, just brainstorming

-- PaulHarvey - 18 Aug 2011

Thanks. All interesting ideas (even though none of these is yet possible where I work, sadly.)

Normally, I put things in topics too, but in this case, the example is just an example. The general case is extracting fields from Table A to use in Table B. And, where I work, topics are expensive. Most webs already contain thousands. Tables are cheaper and easier for managers to deal with.

I've changed the topic title to make that more clear. My original choice of title assumed an implementation detail that may not be required.

If anyone else has ideas, I'd be happy to know what they are.

-- VickiBrown - 18 Aug 2011

You might try RenderTableDataPlugin

-- ArthurClemens - 18 Aug 2011

Hopefully topics will be cheaper in Foswiki 2.0. DBIStoreContrib is the beginnings of an SQL replacement for the grep-based BruteForce search algorithm, and we're using MongoDBPlugin in production for a site with over 200,000 topics (largest web has ~60,000 topics).

RenderTableDataPlugin and FormQueryPlugin should get you some of the way there, though (however, FQP requires DBCacheContrib which really needs a persistent perl, i.e. mod_perl/mod_fcgid environment to be performant, and can be very expensive on RAM for large sites).

-- PaulHarvey - 18 Aug 2011

even file based topics are much faster in Foswiki 2.0.

-- SvenDowideit - 19 Aug 2011

Thanks, Sven... we're getting there.
Have you improved searching when there are thousands of files in a web?

RenderTableDataPlugin looks sweet! -- VickiBrown - 19 Aug 2011