Seperating a table

[adyf]

I have been gradually restructuring a solution that someone else created and then retired and left me to pick up the reins. I am close to getting where I need to be thanks to a handful of extremely helpful people on this forum (you know who you are ).

My dilemma, below is an example of a couple of records in my main table:

Y214 01 08 14

Y232 14 24 32 Q

In hindsight from day 1 (approx 4 years ago), Y214 should have had three seperate records due to having three Hazard Nos associated with it. I can live with it (10000+ records to deal with otherwise) but it makes a sub summary report (based on Hazard Nos) I have in mind not possible.

If I do decide to seperate the data into a table such as Hazard Nos related to each item in my main table, can I pull the 01 08 14 for item Y214 from the three seperate records in my new table back into one field in my main table for one of my regular reports. I would rather have one entry for Y214 in my main table for report purposes. I could then have an accurate sub summary report by Hazard Nos in my new table.

BTW:- Q is an additional pain as this indicates that Y232 has a freehand question(s) associated with it.

[comment]

If I do decide to seperate the data into a table such as Hazard Nos related to each item in my main table, can I pull the 01 08 14 for item Y214 from the three seperate records in my new table back into one field in my main table for one of my regular reports

Yes. Use the List() function to aggregate the values from related child records.

[adyf]

Yes. Use the List() function to aggregate the values from related child records.

Thanks comment. So I would create a calculation field in my main table and use the List() function. Presumably as part of the calculation I can add a space (" ") between each value?

[comment]

Yes. The List() function separates the values by a carriage return, but you can use Substitute() to replace it by any other character/string.

[adyf]

Yes. The List() function separates the values by a carriage return, but you can use Substitute() to replace it by any other character/string.

In addition to the above could I sort the values in ascending order and have the Q value last (if present) as part of this calculation? Or would I need to sort the records in the other table?

[Kevin Kurpe]

In addition to the above could I sort the values in ascending order and have the Q value last (if present) as part of this calculation? Or would I need to sort the records in the other table?

Yes. The easiest way to sort the records is via the relationship.

1. Open the Define Database.

2. Click on the Relationships tab.

3. Select the 'Relationship' (between the two tables) to open the Edit Relationship window.

4. Click on the 'Sort records' checkbox.

5. Select the field that you want to sort by and click the 'Sort Ascending' checkbox.

Here's a screenshot:

http://imageshack.us/photo/my-images/833/relationshipsort.png/

Enjoy!

Kevin Kurpe

Founder

Proponent, llc

[email protected]

[adyf]

Yes. The easiest way to sort the records is via the relationship. 1. Open the Define Database. 2. Click on the Relationships tab. 3. Select the 'Relationship' (between the two tables) to open the Edit Relationship window. 4. Click on the 'Sort records' checkbox. 5. Select the field that you want to sort by and click the 'Sort Ascending' checkbox. Here's a screenshot: http://imageshack.us/photo/my-images/833/relationshipsort.png/ Enjoy! Kevin Kurpe Founder Proponent, llc [email protected]

Thanks Kevin. I've seen this option but always overllooked and used either a script or script trigger.

Welcome to the forum by the way, I notice that you are already an advanced user.

Yes. The List() function separates the values by a carriage return, but you can use Substitute() to replace it by any other character/string.

Which function requires to be innermost? I'm thinking List() but the below isn't quite working.

Substitute (

List (Hazard Nos::Hazard No)

Hazard Nos Combined; "¶" ; " ")

[comment]

Substitute (

List (Hazard Nos::Hazard No)

Hazard Nos Combined; "¶" ; " ")

What is Hazard Nos Combined? The calculation should be along the lines of =

Substitute ( List ( ChildTable::Somefield ) ; ¶ ; " " )

[adyf]

What is Hazard Nos Combined? The calculation should be along the lines of =

Substitute ( List ( ChildTable::Somefield ) ; ¶ ; " " )

I'm getting mixed up because Hazard Nos Combined is a calculation field that = List (Hazard Nos::Hazard No)

I didn't realise that I only have to reference the field that I am working with once despite there being two functions. I thought that I would need to show two fields as there are two functions. Appreciate your help as always.

[Lee Smith]

Hi Kevin,

Yes. The easiest way to sort the records is via the relationship.

2. Click on the Relationships tab.

It probably doesn't matter in this case, but I prefer to do this in the portal setup rather then globally, as you might want to use this same relationship in another portal, with a different, or no sort at all.

Lee

[comment]

I'm getting mixed up because Hazard Nos Combined is a calculation field that = List (Hazard Nos::Hazard No)

You could use another calculation field =

Substitute ( Hazard Nos Combined ; ¶ ; " " )

But if you're doing it all in the same calculation field (and why wouldn't you), you cannot reference the field itself in the calculation; that would be a circular reference.

[adyf]

Worked a treat Comment thank you very much. I now understand what you are saying about using the same calculation field.

I'm now trying to figure out how to create multiple child records from the parent table to assign hazard numbers. I can create one via the relationship LocationsID -< LocationsID but can't create another, it just changes the hazard no field within the same record.

I understand why it does this and think I probably need another relationship and TO. I know that the parent table needs to know its children and it does. However, hows does the parent table identify it's children individually? My child table does have it's own primary key field.

[comment]

I can create one via the relationship LocationsID -< LocationsID but can't create another, it just changes the hazard no field within the same record.

Are you using a portal to enter the child records?

I know that the parent table needs to know its children

That's not quite true: it's the child's job to remember who its parent is. The parent "knows" its children only because they hold up a sign saying so.

[adyf]

Wow that was quick! No, Im not using a portal although I did consider it. My layout is rather full and I can't squeeze a portal on it unless I can use a pop up window. I was rather hoping that I could enter into the child table without using a portal. If not possible I will have to consider a portal.

[comment]

It's possible, but more complicated. If you simply placed child fields on the parent layout, they belong to the first child. So the first entry creates the first child, and anything that happens afterwards only modifies it.

Perhaps you should use global fields for the entry and create the child record by a script attached to a large "Add" button.

[adyf]

It's possible, but more complicated. If you simply placed child fields on the parent layout, they belong to the first child. So the first entry creates the first child, and anything that happens afterwards only modifies it

.

I've considered what you've said, rejigged my layout and put a child table portal on it.

Perhaps you should use global fields for the entry and create the child record by a script attached to a large "Add" button.

The original way I wanted to do it does still intrigue me. As you say a parent only knows their children by a sign that they hold up. Could they hold up a sign in their other hand so that their parent can identify them individually?

I know what a global field is but I'm unsure of your above suggestion.

If I have the following on my ERD:

(Locations) LocationsID -> (Hazard Nos) LocationsID

|

^

(Bespoke Q_A) LocationsID

I won't have a problem running a report from the (Locations) table with related records from (Hazard Nos) and (Bespoke Q_A) will I?

I'm currently making the necessary changes in the background until I'm happy this works. Currently Hazard Nos and Bespoke Q_A are part of the (Locations) table which ought to be in seperate tables for good structure and sub summary reporting.

[comment]

Could they hold up a sign in their other hand so that their parent can identify them individually?

Not sure what you mean by that. A parent can tell its children apart, for example by their position in the related set (cf. the GetNtheRecord() function) or by a unique value such as ChildID.

I won't have a problem running a report from the (Locations) table with related records from (Hazard Nos) and (Bespoke Q_A) will I?

I don't know. It depends on what type of report you want. In general, it's best to report from a child table - but when you have two child tables, that's not possible.

I know what a global field is but I'm unsure of your above suggestion.

See the attached?

---

In version 11, the same could be achieved without the global fields by using a filtered portal.

AddChild.zip

Edited March 26, 2012 by comment

[adyf]

Not sure what you mean by that. A parent can tell its children apart, for example by their position in the related set (cf. the GetNtheRecord() function) or by a unique value such as ChildID.

Yes, that's what I meant.

I don't know. It depends on what type of report you want. In general, it's best to report from a child table - but when you have two child tables, that's not possible.

This worries me. I could try and combine (Hazard Nos) and (Bespoke Q_A) into one table and report from there, but that said I don't think that it will work because not all parent records have child records so some parent records would be missing from my report. Forget that bit, as I only require the (Hazard Nos) predetermined questions and (Bespoke Q_A) bespoke questions on the report.

The report is a competence assessment and if possible I would rather try to keep it in one report rather than go to two child table reports that together form the full assessment. It's strange but it seems that if I have bad structure and have it all in one table (like I do!) it makes reporting easy. If I seperate data (proper structure), reporting becomes more of a challenge.

See the attached?

Thanks very much, it's a very nice concept. I'm not quite up to that level of scripting yet but hope to get there eventually.

[comment]

Filemaker is very much layout-based. As a result, it's not built to do union reports, because that would require switching a report column mid-way. However, there are workarounds: for example, you could use a portal for one of the child tables (if not actually both).

Combining the two child tables into one is another option, but not if the two tables represent two entities. IOW, you should not subvert your data model for the sake of a report. OTOH, you could import the reported records into a common temp table. for the purpose of reporting only.

[adyf]

Filemaker is very much layout-based. As a result, it's not built to do union reports, because that would require switching a report column mid-way. However, there are workarounds: for example, you could use a portal for one of the child tables (if not actually both).

Combining the two child tables into one is another option, but not if the two tables represent two entities. IOW, you should not subvert your data model for the sake of a report. OTOH, you could import the reported records into a common temp table. for the purpose of reporting only.

They could well legitimately be one entity and as such one table will suffice. The combined table could be called (Assessment Questions). They will all belong to a LocationsID from the (Locations) table. Some questions will be predetermined dependent on the Hazard Nos and some will be bespoke/freehand questions. I will just need a field in (Assessment Questions) that will differentiate between the two.

In a combined table if the report in layout mode looked like:

LocationsID Hazard No Question

Hazard No Answer

Bespoke Question

Bespoke Answer

A location could have one or more Hazard Nos Q&A, one or more Bespoke Q&A , a combination of both or none at all. If I set the fields to slide up and reduce the enclosing part I'd be ok wouldn't I?

I like your other workrounds though. I think printing from within a portal has limitations (was discussed in another thread that I started) and the idea of creating a reporting table is an interesting one.

[comment]

A location could have one or more Hazard Nos Q&A, one or more Bespoke Q&A , a combination of both or none at all. If I set the fields to slide up and reduce the enclosing part I'd be ok wouldn't I?

Perhaps I am not visualizing this correctly; I would think that if Q&A were a record, then nothing needs to slide up; the record either exists (and has data) or doesn't.

[adyf]

Perhaps I am not visualizing this correctly; I would think that if Q&A were a record, then nothing needs to slide up; the record either exists (and has data) or doesn't.

You are understanding this correctly of course. I'm thinking of my current report, has large Q & A fields due to the fact that some records have more text in the Q & A fields than others and they are set to slide up.