Flat Checkboxes into New Tables?

filemaker

    Sponsored Links

    Next

  • 1. Normal list view, sorting and selected record
    In a normal list view (no portal view) each colum is attached to a different sort-script step. The selected/active record is different everytime i sort the found set in a different way. Q: What must i change in each sort script so everytime a different sort script is executed the same record is selected/active? Thanks, LP
  • 2. Filemaker and MS Word
    Dear Everybody: I have a client (FM 5.5, Windows 2000) who wants to be able to click a button from a contact record, and have a letter document open up in Word with the contacts name, address, etc. showing up in the header. This is for sending one-off original letters to specific contacts, not bulk form letters. I thought I would accomplish this by exporting the appropriate fields to a temporary tab-delimited data source file, and then have the MS document access this file with merge fields or database fields. Unfortunately I'm not too familiar with these functions in Word, and the help files aren't making it easier for me. It does seem however, that Word can do what I want it to do, if only I can learn how. Questions: 1. Is my general approach reasonable, or is there a much better way? 2. If it is a good approach, can someone give me an overview, from the Word side, of how to set up a Word (stationery?) document that draws data from an outside source? Thanks, Greg --
  • 3. Case Statement Erratic Results
    I have tried using the 'case' calculation to filter results based on various ranges. I am unable to get predictable results i.e. sometimes I will get one label and at other times another lable for similar data. Here is an example of the input. Case(NWS_CIG >= 30 , "H-VFR", NWS_CIG >= 20 and NWS_CIG < 30, "VFR", NWS_CIG >= 14 and NWS_CIG < 20 , "MVFR", NWS_CIG >= 8 and NWS_CIG < 14 , "IFR", "No Data") What I am attempting to do is to get a different response based on a numeric (text) value. Any clues as to what I am doing wrong. The reason I am using text as opposed to numeric values is that not all received data is in numeric format. Do I need to create a calculation to bring the data to a numeric format first? Many thanks p.s. Is there any document with a good description of how one can use calculation scripts. Something that takes you well beyond the basics
  • 4. Suggestions for a new FileMaker plug-in
    Hi Everybody, I am developing a plug-in right now that includes all of the features that I think should have been built into FileMaker but for some reason are not. So far I have: - Running script on field exit - Changing Printer - Altering existing FileMaker menu (add, edit, delete menu, and assign scripts to menu items) - Changing logged in user - Adding, editing, deleteing fields So if anyone has any suggestions of items I missed I would appreciate it. I am not looking for items that go above and beyond base FileMaker functionality, such as file manipulation, barcode scanning, things like that (although if you do have an idea like this I would love to hear it). Just things that most people would agree should have been built into FileMaker. Thank you very much, Ryan

Flat Checkboxes into New Tables?

Postby Albert » Mon, 27 Nov 2006 01:44:35 GMT

I have some "flat" (single table) databases that contain Subject term 
fields formatted as checkbox arrays where one or more keywords can be 
ticked off according to content.

Now I realize that I could make much better use of this information by 
creating a Subject Term *Table* in FMP8 -- I could print out nice 
indexes of subject matter, for instance.

So, I'm trying to figure out how to import the flat field data from the 
original database into a new multi-table database so that each "checked 
off" value in the Subject Term field becomes a single record in the 
Subject Table, right?

I feel stumped -- I seem to recall when importing other files seeing an 
option for splitting "Repeating Fields" into separate records - but what 
about splitting multiple values in a checkbox field into separate 
records?

Thanks for any pointers!

Albert

Re: Flat Checkboxes into New Tables?

Postby Helpful Harry » Mon, 27 Nov 2006 15:10:52 GMT

In article < XXXX@XXXXX.COM >,




You can't split them on importing, that option is only for Repeating
fields, and only because Repeating fields were basically the poor man's
related records before FileMaker went properly relational.

You'll have to use a script that loops through each record creating all
the appropriate related records that are needed. The easiest way to
create these related records is to use a Portal that allows creation of
related records, then the script is fairly simple. Something along the
lines of (as long as there's only one Portal):

    Show All Records
    Go To Record [First]
    Loop
      If PatternCount(CheckboxField, "Option1") > 0
         Go To Portal Row [Last]
         Set Field [Relationship::OptionField, "Option1"]
      End If
      If PatternCount(CheckboxField, "Option2") > 0
         Go To Portal Row [Last]
         Set Field [Relationship::OptionField, "Option2"]
      End If
      ...
      If PatternCount(CheckboxField, "OptionX") > 0
         Go To Portal Row [Last]
         Set Field [Relationship::OptionField, "OptionX"]
      End If
      Go To Record [Next, Exit After Last]
    End Loop



Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)

Re: Flat Checkboxes into New Tables?

Postby Bill » Mon, 27 Nov 2006 22:43:21 GMT

n article < XXXX@XXXXX.COM >,
Albert < XXXX@XXXXX.COM > wrote:


First, the actual content of the field you have formatted as "checkbox"
is the word associated with each checkbox. If you put a new occurrence
of that field in a layout, and leave it formatted as an Edit box, you
will see the actual values.

You defined a value list for the Subject Terms, and the checkboxes
simply choose values from that list to go into the field.

The Term table should have at least two fields: A text field to hold the
term itself (which I will call TermName), and a serial number field to
hold the primary key field that will ultimately be used to relate the
Term table to the "old" table (which you have not given us a name
for,and will simply call "Old_Table"). I will call this serial number
field kpTermID.

You will also need to define a number field in Old_Table to hold a value
of TermID, to make the ultimate relationship between the two. Call this
number field kfTermID.

I see three ways to create the Term table.

Method one:

SImple and direct: Create a new table for Term, and simply create a new
record for each term, typing the term into the TermName field for the
new table. This is easy if you have relatively few different Terms.

Method two:

Copy the already defined value list of Subject Terms to a text document.
You can do this by simply selecting the content of the value list,
copying it, and pasting it into a text document. Then import the Text
document into a new table. This will create one record for each of the
Terms.

Method Three:

Define a new Term table, and require in the field definition that the
values in the TermName field be unique. Then import records from the
"old" table into the new table, importing only the Term field.

Any of these three methods will produce a new table of Subject Terms,
with one term per record, all being unique.


Relationships:

After you have that done, then set up the relationship. The first
relationship should be

Old_Table::SubjectTerm = Term::TermName

You can stop here, and set up your value lists and layouts based on this
relationship. However, that leaves a relationship based on a field whose
content is editable. This is not ideal, for various reasons. To go the
extra step of getting a relationship based on an uneditable field,
proceed as follows:

In the Old_Table table, do Show All Records, then select the field
Old_Table::kfTermID and do Replace Field contents, by calculation, as
follows:

Old_Table::kfTermID = Term::kpTermID

This populates the kfTermID field in the Old_Table records with the
corresponding value of kpTermID from the related Term records.

After this define a new relationship between the two tables based on
TermID:

Old_Table::kfTermID = Term::kpTermID

and get rid of the other relationship based on TermName.

Now you have a properly defined relationship between the two tables,
based on the uneditable key field kpTermID. It is a one-to-many
relationship, one Term record to many Old_Table records.

You can now get rid of the field SubjectTerm from Old_Table, and put the
field Old_Table::kfTermID and the related field Term::TermName on its
place.

You can now change the value list of SubjectTerm to be dynamic, based on
the field value of Term::kpTermID, showing the second field
Term::TermName, and showing only the second field. format the field
Ol

Re: Flat Checkboxes into New Tables?

Postby Bill Marriott » Tue, 28 Nov 2006 00:12:44 GMT

You have FileMaker 8, right? Harry's script is overkill (but probably the 
only way to do it back in FileMaker 6); and I don't think "Method Three" 
described by Bill C will actually work.

Check boxes put multiple values into a single field, with a carriage return 
between them. Your initial thought was to "split the repeating fields" which 
was correct except there wasn't a repeating field to split (which is why 
FileMaker didn't offer you the option).

So, for the purposes of migration you need a repeating field. The following 
calculation will give you one:

GetValue(Extend(X); Get(CalculationRepetitionNumber))

Where "X" is your check box "Terms" field. Just set up your calculation to 
have a Text result and however many repetitions are required to hold the 
maximum number of key word terms. The calculation will "break out" the 
values one per repetition, essentially transforming field X into a repeating 
field. Then you can import and split the values like you wanted to in the 
beginning.

Good luck!













Re: Flat Checkboxes into New Tables?

Postby Albert » Tue, 28 Nov 2006 01:53:20 GMT

In article < XXXX@XXXXX.COM >,




Wow -- thanks to all three of you for responding in such depth.  Apart 
from actually getting this task accomplished, this is a fascinating 
tutorial on various Filemaker strategies and tactics!

So, I create a new, repeating text field in my original file and define 
it with the calculation you specify there.  Is 
"CalculationRepetitionNumber" the literal text I put in there, or is 
that a number I enter according to how many repetitions I determine that 
I need (the max number of checkboxes checked in any record)?  So, for 
instance it might look like:

GetValue(Extend(SubjectKeywords); Get(10))

Is that right? I'm just unclear on what that 
"CalculationRepetitionNumber" means, exactly.

Thanks again!

albert

Re: Flat Checkboxes into New Tables?

Postby Albert » Tue, 28 Nov 2006 02:55:32 GMT

Nevermind that last question -- I tried it out and everything works 
great!

Woo--hoo!

albert

Re: Flat Checkboxes into New Tables?

Postby Bill » Tue, 28 Nov 2006 07:00:50 GMT

In article < XXXX@XXXXX.COM >,










As usual, Bill Marriott has given excellent advice.

-- 
For email, change <fake> to <earthlink>
Bill Collins

Similar Threads:

1.using the "copy from" command to load tables from a txt flat file

2.MaVerick Software Foundation Announces New Members, New Website, New Features, New Name, New Focus

3.FM6: Create new records by scripting in another table

Hi,

I have 2 tables and have to extract a lot of data from table 1 into a
list. This list is table 2. With a script I look in table 1 for
combination of field values. When these values match I want to create a
record in table 2 with the matched values from table 1. The selecting
script runs in table 1.

Does anyone have an example of an script how to do that?

Regards,

Marcel Kollenaar

4.New Tables

With FM6 and it's system of using files I often just duplicated a file, 
renamed it and it's field names. This just to say I miss the ability in FM7 
to duplicate a table. Would save a lot of work.

-- 
Keep Well,
Ursus 


5.Import Data from flat-database to relational-database

Before I start I need some tips and pleas excuse my bad english.

I have two databases, one is flat and one is a relational database.

example	 database 1

name
surname
street
city
country

example database 2

table NAME

prim_key
name
surname

table ADDRESS

adr_key
street
city
country

Both databases are filled with existing records. I want to import from 
time to time records from the flat database to the relational database.
Whats the best way to do this with filemaker?
I also could use a relational database instead of the flat one, but i 
think the problem resulting from that is, how to create the correct 
adr_key and prim_keys, because in the database are existing keys that 
shouldn't be destroyed?

Does anyone know how to do this?






6. New Record script step and related tables

7. Displaying only a new record in a table

8. new user question: format the table view



Return to filemaker

 

Who is online

Users browsing this forum: No registered users and 64 guest