"go to portalrow" script problem

[Mountainoak]

Hello,

i've created a database registrering surgery data. A patient will under go a certain surgery and will have certain techniques applied. I've created portals for each of these techniques. These portals have a script to add the selection to one join table. This works allthough for one patient this script creates for each technique a new record in the join table. To keep the number of new records down I tried to solve this by adding a "go to portal row" scripts step so each technique is added in the same portal row.

This creates two problems:

1) when I don't make a selection in the first portal there will be no record at all in the join table.

2) when selecting more than one technique in each portal it doesn't create a new record in the join table but replaces the first selection.

Could somebody help me please to adjust the script.

I believe the script should be something like:

if first portal row is empty create new record.

if not add data to existing portal row.

if portal column in row 1 is not empty go to next portal row.

I attached the database.

Thanks

scriptvraag_1.fp7.zip

[comment]

These are not problems at all:

• There is no need to "keep the number of new records down". On the contrary, you want the data in separate records, so that it can be easily analyzed. The amount of data you record is the same anyway - it's only a question of how it's divided.

• There shouldn't be a join record until one is NEEDED, with known data. Dummy records with no data skew reports.

However, I believe you have way too many tables, table occurrences and scripts. I think this could be much simpler - see the examples in the attached file.

---

keyword: PortalToPortal

PortalToPortal.fp7.zip

[Mountainoak]

thanks for your reply.

I looked at your sample file and believe I'm using one portal "Attributes by Category" and the other portals (TO called ALL_...) as "fixed Category". I chose this because of easier data entry ( don't need to select the "categories" all the time).

The other TO I created are (called ..._REf) I use for adding new data to the portal list whilst being in the portal.

I couldn't add data in your portal without creating the extra TO's.

The script however looks easier.

As you say the extra records don't matter my question changes.

My goal is to use the join table for reporting. I need to create a pivot table( tried in filemaker but am far too novice) so I`m trying in excel. Before I export into excel I need to import data first and add some data. Then export for reporting. When I import from excel I`m afraid that each patient will receive a new patient_id per technique applied with the current setup.

Do you have a solution for this. Perhaps I can avoid it by creating patient_id myself in excel and don't let filemaker create them.

Secondly how can i make filemaker display all techniques used per patient and export them into excel in one row.

[comment]

I couldn't add data in your portal without creating the extra TO's.

If you mean the portal to the join table, you can create new records by entering data into the last portal row (in addition to scripted creation).

I`m afraid that each patient will receive a new patient_id per technique

I don't understand this. Each new patient ("Object") gets a unique and permanent PatientID. This same ID is then used in the Patients table and in the join table (and in any other table that would be a child of Patients). The relationships couldn't work without this.

how can i make filemaker display all techniques used per patient and export them into excel in one row.

This is possible, but not quite simple. Why cannot be exported as a column?

[Mountainoak]

If you mean the portal to the join table, you can create new records by entering data into the last portal row (in addition to scripted creation).

Can you explain this further please as I don't see a free last portal row nor can I select the last portal row. The only field editable is the notes field in the join portal.

This is possible, but not quite simple. Why cannot be exported as a column?

How does one export in colums?

[comment]

Click the first (topmost) empty portal row, in the AttributeID column. Select an attribute. You have created a new join record, and now you can add notes to it. You can also type a note first (again, in the first available portal row) and select the attribute later. Any data entered into the last (i.e. first empty) row causes the creation of new record (that's how this relationship is defined).

How does one export in colums?

That is the default for any database or database-like format: fields are columns, records are rows. Spreadsheets are flexible in this aspect, databases are not - so it's best to adjust the spreadsheet orientation to the database, instead of the other way round.

Or perhaps I don't understand what's your purpose here.

[Mountainoak]

Click the first (topmost) empty portal row, in the AttributeID column. Select an attribute. You have created a new join record, and now you can add notes to it. You can also type a note first (again, in the first available portal row) and select the attribute later. Any data entered into the last (i.e. first empty) row causes the creation of new record (that's how this relationship is defined).

We talk about different things. I refer to the other portals e.g the "All Attributes". Say I have a new attribute called Zorro. How can I add this to the database whilst being in the objects layout?

Got the spreadsheet working.

[comment]

It depends. In general, you can add records directly in the portal, if you define the relationship to 'allow creation of records' on the child side. That will work for the bottom two portals, but not for the top "All Attributes" one, because this relationship uses the "x" relational operator.

You can always add a global field, put data in it and have a script take this and create a new attribute record with it - very much the same as the current script does for the join record.

[Mountainoak]

In general, you can add records directly in the portal, if you define the relationship to 'allow creation of records' on the child side.

I tried this on the "attributes by category" portal and there is a extra line in the portal for adding new data but I can't get it "clicked" so I can actually enter data in the portal.

Would you be so kind to have a look at the attached file and tell me what I'm doing wrong?

Thanks