Portal Valuelist lookup from a script

[Peter Lehrack]

Good people of FM-knowledgeland.... I need help!

I'm trying to get a script to assign a field to the value of an item in a valuelist.

I have customers who have many accounts, the account numbers are stored in a portal with the first field set to the account name and the second field to the account number.

I need a script to look for the account name in the portal of a given customer and assign the associated account number to a field.

In order to make it as complex as possible, the account name and number fields are repeating fields (10).

Thank you, any insights into this process would be very much appreciated.

Peter

PS: While I'm at it.... any way to display a variable value in a layout without using a field? I tried Merge Fields.... but as the name infers, they seem to require fields.

[comment]

I have customers who have many accounts, the account numbers are stored in a portal

...

In order to make it as complex as possible, the account name and number fields are repeating fields (10).

Such arrangement makes no sense. If you have a portal, why the repeating fields? If a customer has 10 accounts, there should be 10 related account records, one for each account. Then you can have a button in the portal that does:

Set Field [ Customers::SomeField ; Accounts::AccountNumber ]

any way to display a variable value in a layout without using a field?

You could use a web viewer for this.

[RalphL]

Good people of FM-knowledgeland.... I need help!

I'm trying to get a script to assign a field to the value of an item in a valuelist.

I have customers who have many accounts, the account numbers are stored in a portal with the first field set to the account name and the second field to the account number.

I need a script to look for the account name in the portal of a given customer and assign the associated account number to a field.

In order to make it as complex as possible, the account name and number fields are repeating fields (10).

Thank you, any insights into this process would be very much appreciated.

Peter

PS: While I'm at it.... any way to display a variable value in a layout without using a field? I tried Merge Fields.... but as the name infers, they seem to require fields.

Peter,

To answer your PS: NO!

I suggest that you re-think using repeating fields. Use a related table instead.

Do you mean: I am trying to get a script to assign the value of an item in a valuelist to a field?

[Peter Lehrack]

oops, turns out that I was a bit confused... I had a portal on the layout but only as a background to my vertically oriented repeating fields.

I came up with a solution to the issue, I'm hoping that there is a more efficient way to do what I need. What I did is as follows:

Set Variable [$Loop_Count; Value:1]

Loop

Go to Object [Object Name: "Cust_Account_ID"; Repetitions: $Loop_Count]

Set Variable [$ID; Value:Get ( ActiveFieldContents )]

Exit Loop if [$ID="DOT"]

Set Variable [$Loop_Count; Value:$Loop_Count+1]

End Loop

Go to Object [Object Name: "Cust_Account_Number";Repetition: $Loop_Count]

Set Variable [$$Account_Number; Value:Get ( ActiveFieldContents )]

So, essentially... I go to the repeating field "Cust_Account_ID" which I assigned a name to and step through each repetition until I find the one that matches "DOT", I then go to the field "Cust_Account_Number" with the same repetition and assign that value to a global variable for later use in my script.

It seems like there should be a better way, any thoughts?

Thanks,

Peter

Edited January 2, 2009 by Guest

[comment]

It seems like there should be a better way, any thoughts?

Yes, there is - and it is described above.

[Peter Lehrack]

Comment,

Thanks for your response. As I am not overly cleaver and very new to FM and databases in general, I can't say that I understand what you are getting at with that approach. Maybe a bit more detail will toggle the lightbulb above my head.

My main question is how would my script know which record for the customer is the one that I'm looking for (in my example "DOT")? Would I have to do a find?

missing something,

Peter

[comment]

It would be easier to explain if we had a bit more detail from you. So far all we know that there are customers and that a customer has accounts. Now, what is this "DOT", where is it coming from and where is it supposed to go? At first, I thought you were asking how to pick the account number of an account that you clicked on.

[Peter Lehrack]

Comment,

Thanks again for the response, the situation is as follows. I deal with customers who have accounts with the federal government and with various state agencies. The information is similar to the following:

Company Name: Ted's Widgets

ACCT_ID - ACCT_NUMBER

FEIN - 99-999999

DOT - 1232345

NYSTATE - 123123123

NJSTATE - 998877

TXSTATE - 12121212

ALSTATE - 2215TED

The script that I'm currently writing will take Ted's Widgets order information and fill out an application for a given locality. I scanned the individual application forms and made a layout for each in order to populate them with the order data plus the account information data.

Say the current order is for Texas, my script will select the texas application form and fill in the order data, I need to also fill in the FEIN, DOT and TXSTATE account numbers as required on the form. I don't have the order table lookup and store the account data as only a small number of the hundreds of localities I deal with require this information, so storing it on every order form seemed inefficient.

Each customer has different account locality requirements, so I can't standardized them. The only common account types are the FEIN and DOT.

Let me know if that makes sense and sheds any light on my question.

Thanks again,

Peter

Edited January 2, 2009 by Guest

[comment]

I am still not getting all the nuances here, but have a look at the attached - it shows two possible methods of picking the account number, and you can probably come up with a few more based on the same principles.

Note that the global field and the additional relationship are not required for the #2 (portal/script) method.

SelectAccount.fp7.zip

[Peter Lehrack]

Thanks, I'll take a look.

[gene.seeker]

I think I have a related problem with my script. It selects the correct row and field in my portal and also selects the correct item in the value list, but then it hangs. If I manually click or hit return then the portal field can be populated, but I really want to have a script that automatically prepopulates the values in the portal with defined values.

To provide more information: This is a database to keep track of students and their enrollment in courses. I have the following tables: STUDENTS, JOIN and COURSES. The portal is on a STUDENTS layout. The first column of the portal is a value list that gets a list of courses via the JOIN table. The value list contains unique values coming from the key field in JOIN passed from COURSES. The remaining columns just populate the related information (coursename, coursenum, credits). This all works fine manually, but I've noticed that I have to populate the portal rows sequentially, and sometimes I don't want to do that. So, what I want to do is to pre-populate the portal entries using generic enrollments and then I'll go back and edit. Is there a way to get a script to do this?

[gene.seeker]

I should add that I've tried the following

1. Insert Text ["Mytext"]

2. The same with a carriage return after the text

3. Set Field [sTUDENTS::cleanout] where the cleanout field contains an entry I set.