Copy Field contents from one Table to another table - FileMaker

1
votes

I am new to Filemaker pro. I am working with Filemaker pro 13.

My database contains 3 tables:

  • category (fields = _pkCatID & CatName)
  • subcategory (fields = _pkSubcatID , _fkCatID & SubcatName)
  • books (many fields including _fkSubcatID)

I have no problems in conditional value lists, so making two popup menus in books layout for categories and subcategories was successful.

But I want to put both categories and subcategories in one menu/sub-menu using 2empowerfm Menu Popper plugin.

I created a new field in subcategory table to store a calculation to be used in the value list of the plugin. The calculation is = CatName & ">" & SubcatName & ";" & _pkSubcatID . So the returned value when choosing in books layout will be "_pkSubcatID".

The problem is CatName is not in Subcategory table, and if I choose it from the related table Category, I can't make the calculation "stored" which is a requirement to use a field in a value list.

So, I need to copy the field CatName from category table to a new created field in subcategory table. I don't know how to do it.

2
copyfieldfilemaker
Use a lookup? (I am not familiar with the plugin and its requirements.) – michael.hor257k
@michael.hor257k Thank you. It works. – Granadian

2 Answers

0
votes

You just need to create a lookup field in your subcat table pointed to the category name in the category table.

  1. Create a field in the sub-cat table called "Category"
  2. Click on Options
  3. Auto-Enter Tab at the bottom, check "Looked-up value"
  4. Select the correct starting (subcat) and related (Cat) tables and select the name field for the Category.

That is all.

To populate this for existing records click into the _fkCatID field on a subcat layout after showing all records and in the menus select Records->Relookup Field Contents

0
votes

@Michael Wallace answer is correct and that solution should work.

I'd add however that if the table is likely to become large (and it could do if you're cataloguing books for a library) then I'd suggest you run some tests on a fake large data to see if this menu technique holds up (especially if you are serving over a network). Running two global search fields with an executeSQL lookup for subcategory within category would be more efficient in a big data set - this technique is well described here and other places: http://forums.filemaker.com/posts/c4ed6f9923