Jump to content

Calculation field: substitution of key with value


This topic is 1831 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

Hi everybody.

I'm a newbie with filemaker, but I'm using it with satisfaction.. 
Sadly, I've made a mistake with my first project and I'm trying to correct it without changing my database, because now it's been populated.
I've create two tables (A and B). In table A I've create a list of value, each of them with a primary key. In table B I've create other records, and in every record there is a field (named "fKTableA") in which I've selected all the values from the table A that are related with the record of table B.

The problem is that now I'm trying to create a report, but (naturally) in "fkTableA" there a list of numbers instead of the values.

Is possible to create a calculation field that substitutes for each number in "fKTableA" the correspondent value of the Table A?

Graphically:
NOW fKTableA: 12          Expected results fKTableA: Tizio

                          34                                                        Caio

                          25                                                        Sempronio

Thanks in advance for your help and excuse me for my poor english.. ^_^

Alex

Link to comment
Share on other sites

This is rather confusing. Suppose you have a Parent table that look like this:

ParentID       ParentName
12                   Tizio
34                    Caio
25                    Sempronio

and a Child table that looks like this:

ParentName   Other fields...
Tizio
Caio
Sempronio
Tizio
Caio
Caio
Sempronio

you should do the following:

  1. Define a ParentID field in the Child table;
  2. Define the relationship between the two tables as:
    Parent::ParentName = Child::ParentName

     

  3. Go to a layout of the Child table, show all records, click into the ParentID field and select: Records > Replace Field Contents… > Replace with calculated result =

    Parent::ParentID

     

  4. Change the relationship to:

    Parent::ParentID = Child::ParentID

     

  5. Delete the ParentName field from the Child table.

 

Make sure you have a backup before you try this - there is no undo.

 

  • Like 1
Link to comment
Share on other sites

  • Newbies

Thank Comment,

Your solution doesn't work for me. I've tried to simplify a little more the situation, I think..

Table A has those fields: pKTableA (primary key) and valueA (text value).

Table B has those fields: pKTableB (primary key), fKTableA (foreign key populated with pKTableA), other values..

When I was populating table B, I use checkbox input format (that allow to select multiple values) for the fKTableA, showing the related value (valueA) in the field itself. Now, when I try to create a report, filemaker correctly show me the content of fKTableA: the numeric key of table A. I would like to show, instead of a list of numeric values for each record, the list of related text values.

The calculation field should make a calculation like this: For each line of the field fKTableA get the number and substitutes it with the related text value.

I hope that I explain it more clearly.. Thanks in advance

Link to comment
Share on other sites

I would be grateful to you if you used meaningful names for your tables instead of abstract names likes Table A and Table B. We are people here, not machines.

If I finally understand the issue correctly (?), you want a calculation field (result is Text) =

List ( TableA::valueA )

Alternatively, you could use a portal to show the related records and their text values.

--
Keep in mind that this form of many-to-many relationship without a join table is very limited in terms what you can do in your report.

 

Edited by comment
Link to comment
Share on other sites

This topic is 1831 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.