topic Re: Creating a new field from multiple tables in App Development

Creating a new field from multiple tables

wschefter — Tue, 26 Mar 2019 21:20:10 GMT

Hi all;

I have created a new app and within this app I am creating new fields. I have been doing this through a script, something new for me. Within the script I am creating new fields based on the data within the excel spreadsheet. I have been largely successful in this up until this problem.

In column B of my spreadsheet I have Customer and in column A of my spreadsheet I have Plant. What I am doing is creating a new field called "region" and multiple Plants can be within a region. That was easy enough to create the field for (if/then statement joined with or). My problem comes when I have to create a region that is both plant and customer numbers.

For a normal Region the script I have created is this:

If([Plant] = 'xxxx' or [Plant] = 'yyyy' or [Plant] = 'zzzz', 'Region A'. This works as planned returning Region A.

Where I cannot get the region to work is when I am combining Plant and Customer. Here is how I have written the script:

If([Plant] = 'xxxx' or [Plant] = 'yyyy' or [Plant] = 'zzzz' and [Customer] = 'aaaaa' or [Customer] = 'bbbbb', 'Region B'. This does not provide any data, or even the name of the region, when I load the data. I am trying just to pull the data for those customers within these plants.

Any help in what I am doing wrong would be appreciated.

Re: Creating a new field from multiple tables

dwforest — Tue, 26 Mar 2019 22:28:23 GMT

Need some parenthesis to dictate order of tests...

If(([Plant] = 'xxxx' or [Plant] = 'yyyy' or [Plant] = 'zzzz') and ([Customer] = 'aaaaa' or [Customer] = 'bbbbb'), 'Region B'.

Re: Creating a new field from multiple tables

amrinder — Wed, 27 Mar 2019 06:05:05 GMT

Hi,

You should build separate table for region and form association with other data. that will be more convenient and you don't have to write multiple if conditions. Separate table can be maintained using INLINE Load or Excel.

For Example:

Load * Inline

[

Plant, Plant_Region

xxxx,RegionA

yyyy,RegionA

zzzz,RegionA

cdfg,RegionB

dfeg,RegionB

];

Load * Inline

[

Customer, Cust_Region

Cust01,RegionA

Cust02,RegionA

Cust03,RegionA

Cust04,RegionB

Cust05,RegionB

];

Please try and let me know.

-amrinder

Re: Creating a new field from multiple tables

wschefter — Fri, 29 Mar 2019 22:28:30 GMT

Hi guys. It has been a busy week and I have not been able to get back to this.

Thanks for the replies. Unfortunately the brackets did not work. As well I am not sure how exactly to write the inline. When I attempt it as above it won't load. I know I am doing something wrong, just no idea what. I am pretty confused.

Overall I am looking to not have to do a v-lookup in excel and then load the data.