Sort categories within the record

[J.P.]

Hi,

I

[CobaltSky]

There are likely to be several ways you could approach this, depending on how your information is stored currently (eg whether the categories are in one field separated by carriage returns, in separate fields, in a repeating field etc etc).

Regardless of this, it is likely that the desirable end point will be one where your database creates individual PO records for each category, in a related file, linking them back to the originating record via a one-to-many relationship.

It is also possible that the best way to get to that point is first to set up a suitable related file (with PO layout etc) and relationship, and then to construct a script which generates a PO record in the related file for each category in turn.

Exactly how you would need to structure such a script depends on what your current set-up is and also on how you want to go about maintaining the system and generating new POs over time.

[J.P.]

Thanks for your input Ray!

But I would like to know how the script can be made... can you show me an example?

The category fields are separate fields and not repeating.

[CobaltSky]

Well, there are a number of quite different ways to approach it, and you've not provided enough information for me to be able to recommend one approach over another. However, since you ask, I will describe one approach by way of an example.

Assuming you have:

- Created a RecordID field (Calculating with the formula Status(CurrentRecordID)) in your current file,

- Used the RecordID field as the unique key field for a relationship called 'POfile' to your new PO file,

- Enabled the "Allow creation of related records" option for the relationship

- Placed a scrolling portal based on the relationship on the layout(s) of your main file that you want to work from (eg run the script from),

- Created a text field called 'Category' in your PO file,

...and assuming also that the secondary key field in your PO file (the one the relationship points to) is a data entry field, and that you can pick up most of the data you need for your purcahse orders via a link back to your existing file.

Then your script might be constructed as follows:

If ["not IsEmpty(Category1)"]

Go to Portal Row [Last]

Set Field ["POfile::Category", "Category1"]

Exit Record/Request

EndIf

If ["not IsEmpty(Category2)"]

Go to Portal Row [Last]

Set Field ["POfile::Category", "Category2"]

Exit Record/Request

EndIf

If ["not IsEmpty(Category3)"]

Go to Portal Row [Last]

Set Field ["POfile::Category", "Category3"]

Exit Record/Request

EndIf

If ["not IsEmpty(Category4)"]

Go to Portal Row [Last]

Set Field ["POfile::Category", "Category4"]

Exit Record/Request

EndIf

In this example, any additional information which you wish to transfer to other fields in the PO file at the time of creation of the records would be included as additional Set Field [ ] steps immediately underneath the Set Field ["POfile::Category", "CategoryN"] step for each category (ie before the Exit Record/Request step).

In the absence of details from you I've had to make rather a lot of assumptions, any of which may or may not be applicable to your requirements.

Moreover there are at least two other fundamentally different ways to approach the task (not to mention a number of variations of each basic approach) and the above example may not be the ideal one for you depending on where the information comes from and how you want to work with the files and the information once they are operational.