This question about Topic Markup Language and applications: Answered

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 smile

-- 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. smile

-- SvenDowideit - 19 Aug 2011

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

RenderTableDataPlugin looks sweet! -- VickiBrown - 19 Aug 2011

QuestionForm edit

Subject Topic Markup Language and applications
Extension SpreadSheetPlugin
Version TWikiRelease04x02x04
Status Answered
Related Topics
Topic revision: r7 - 19 Aug 2011, VickiBrown
The copyright of the content on this website is held by the contributing authors, except where stated elsewhere. See Copyright Statement. Creative Commons License    Legal Imprint    Privacy Policy