Position and Delimiter Return

[kgltap]

Hi this is my first post and I am stuck

There are two tables, one for storage of codes and another where I would like to have a calculation reading from the other table.

The second table is the table of codes and can look like this:

Number Issue

1 Service Call

2 TV Issue

3 Mount Issue

etc...

And in the first main table I want to be able to enter in the number and it pull the issue from the other table.

i.e. 1 will return service call.

Here's the thing, that is really easy but the problem lies in what if there are more than one issue.

I would like to be able to type in one field,

1; 3 and the second field returns service call & carriage return & Mount Issue

1; 2; 3 would return all three issues separated by a carriage return for each issue

2; 3 would return TV Issue & carriage return & Mount Issue

2 would return just TV Issue

Any thoughts?

[bcooney]

Welcome to the forum!

It would be helpful to know the Main table's real name. We like as much info about your real situation as possible. Anyway, what you really need is a portal that stores the related "Actions".

So, you need three tables:

Main (named appropriately, Customer?)

Actions (related to Main by ID)

Codes (which you have, ID and Name).

The Actions table will have:

__kP_ActionID

_kF_MainID (its parent ID)

_kF_CodeID (can be set using popup based on a value list of Codes (ID Name).

Have a look at portals. Each action will be a record in the portal.

Edited December 22, 2009 by Guest

[kgltap]

I am actually familiar with portals and how the relationship works and that is not the route I would like to go. I really am looking for a custom function, using an array (maybe) of some sort. Either that or a calculation field that uses some standard functions that I havent figured out how to work them yet.

But you asked for the real life situation so here's what I am doing. I need to import excel into filemaker. The excel is coming from an external vendor and need to be easy enough that they can use it. We have a database that contains individual offices. We send out mystery shoppers to audit those offices to make sure that they are in compliance. An office can have more than one issue (hence the suggestion for a portal). However, an office can have more than one audit and I would like to avoid a portal within a portal, since I already have a portal set up for Audit.

You are right about three tables, I didnt catch on to that.

Offices

Audits

Audit Codes

I would like to keep things unique to the offices. The audit table contains: Office ID, Audit ID, Code, and Issue.

The Audit Codes table just contains the Code (number) and the Issue..

Code 1 is an issue of "Service Call"

Code 2 is an issue of "TV Issue"

Code 2 is an issue of "Hours Issue"

And so forth. So here's where I am stumped. If there are multiple codes in that one field, then it should return each issue separated by a carriage return so that the import reads correctly. It needs to be this way because the import is going into checkboxes and I am looking to automate those checkboxes being populated.

So for every audit that gets imported in, I would like to standardized the code in one field

Code

1

2; 3

3

1; 2

And the output being whatever the relational issue is

Issue

1 - Service Call

2; 3 - TV Issue & Carriage return & Hours Issue

and so forth.

Make sense?

[bcooney]

I understand, but from the standpoint of "one field/one fact" your approach doesn't make sense.

[comment]

An office can have more than one issue (hence the suggestion for a portal). However, an office can have more than one audit

Then the proper structure would be:

Offices -< Audits -< Issues >- IssueCodes

Now, it IS possible to do without the Issues table and stick a return-separated list of issue codes into a field in Audits. But then there's no place to write anything specific to the issue itself - all you can do is show the related code meanings in a portal to IssueCodes.

[kgltap]

and that's exactly what I want!! How do I do that? Is a CF with an array built in the right approach?

[comment]

Not sure where you are stuck with this. The field listing the codes should be simply a text field - if you like, you can format it as checkboxes using a value list of codes and their meanings.

For importing from say a comma-separated cell, set the field to auto-enter a calculation (replacing existing value) =

Substitute ( Self ; [ " " ; "" ] ; [ "," ; ¶ ] )

and enable auto-enters during import.