Hello everybody! Im stuck on some algorythm.....

[Michael_Avenilo]

Hello everybody! Im stuck on some algorythm… hope to get some inspiration or tips on how to solve it. thanks in advance for the participants (if will be any :D). So, I’m building a form for my field service drivers to collect data about payments they recive from customers.

The form opens new field to fill only if the previous is filled.

Now, one part of the data is figuring out the PAYMENT METHOD got recived (Bank Transfer\Cash\Check\Credit Card).

According to the chosen method ( Enum with named payment methods )

The relevant PRICE columnd should be shown(Bank Transfer\Cash\Check\Credit Card). MANAGED TO DO IT. Next step to show the CUSTOMER column (enum with names) ONLY if PRICE column is NOT BLANK!

Works only with the first payment method (BANK). all other doesnt react when filled in = dont open the CUSTOMER column… I tried the OR\AND still not working

Any idea how to make other methods to react ?

[Sdfaheemuddin]

According to my understanding you have something like this.

Column 1: Payment method (Enum Bank Transfer\Cash…)

Column 2: Price Bank Transfer (Show If Payment method = Bank Transfer)

Column 3: Price Cash (Show If Payment method = Cash)

Column n: Customer Name (Enum) and so on

If this is the case then make a virtual column named SUM with app formula = [Price Bank transfer] + [Price Cash] + …

In show if of column Customer Name give formula [SUM]>0

[Steven_Coile]

If all you want to do is hide a column until a preceding column has been filled in, add an expression to the latter column’s Editable_If property that tests the former’s status. For instance, for the PRICE column, set Editable_If to =ISNOTBLANK([PAYMENT METHOD]).

[Michael_Avenilo]

+Steve Coile Got it! Thanks!

I’m trying your suggested method through Editable_If property But seems to be stuck and not moving on to payment input column after method is being chosen.

Although, the same formula seems to be working if input in SHOW IF property! if add CONTAINS expression !

ps. how do you BOLD text here in G+?

[RezaRaoofi]

@Michael Avenilov add * before and after word(s) and it will be formatted as bold!

[Michael_Avenilo]

Thank you all for your valubale tips! im using AppSheet about a week, and seems to be its very flex and customable app platform to use!

Glad to have active community users

Hope to be useful in the future too

[Michael_Avenilo]

@Michael Avenilov

The circled in red should remain blank after form is saved and data is filled in the Worksheet

[Michael_Avenilo]

@Michael Avenilov

Like this. so the formula in the right would work (TOTAL DEBTH)

[Michael_Avenilo]

@Michael Avenilov

Solved. Some of formulas from the Headers was copied to the Column’s Spreadsheet properties, which corrupted the correct data input! Earned my sleeping hours for 2day.

[RezaRaoofi]

What was the condition expression you entered for Customer Enum that did not work?

[Michael_Avenilo]

@RezaRaoofi

I tried the ISNOTBLANK[BANK TRANSFER] AND ISNOTBLANK[CASH ] AND ISNOTBLANK[CHECK] AND ISNOTBLANK[CREDIT CARD]

Tried also the same with OR expression. works only with the first ISNOTBLANK rule. the rest of methods are ignored

[Michael_Avenilo]

+Steve Coile this is what i did… it works with the first condition

=ISNOTBLANK([PAYMENT METHOD]) BUT the issue is that there are 4 methods of payments. And it opens CUSTOMERS column only after BANK method is filled. if i enter value in CASH \ CHECK \ CREDIT CARD the CUSTOMERS column is not showing…

[RezaRaoofi]

Try this: OR(ISNOTBLANK([BANK TRANSFER]), ISNOTBLANK([CASH]),

ISNOTBLANK([CHECK]),

ISNOTBLANK([CREDIT CARD]) )

[Steven_Coile]

@Michael Avenilov, you said you tried ISNOTBLANK[BANK TRANSFER] AND ISNOTBLANK[CASH ] AND ISNOTBLANK[CHECK] AND ISNOTBLANK[CREDIT CARD].

It is incorrect to put AND between the things being tested as you did (even if Expression Builder accepts it). The things being tested (ISNOTBLANK(…)) go within parentheses after AND, as in AND(ISNOTBLANK(…), …). Same with OR.

[Michael_Avenilo]

@Sdfaheemuddin It worked! added Virtual Column SUM. reffered to it CUSTOMERS with show if ><0 ! Genious!

[Michael_Avenilo]

@RezaRaoofi This method is working also !!!

i made a copy app to test your method also.

changed the CUSTOMER’s shof if condition to

OR(ISNOTBLANK([BANK TRANSFER]), ISNOTBLANK([CASH]),

ISNOTBLANK([CHECK]),

ISNOTBLANK([CREDIT CARD]) ) and all payment methods now proceed to CUSTOMER column

THANKS !

[Michael_Avenilo]

p.s. While waiting for your suggestion guys, i reorganised the whole Worksheet and combined the PAYMENT METHOD in the same row as the SUM changing the whole Form structure (removed 4 Payment Method colunmns and added single SUM column insted) simplifying the Form this way (i think…).

Making this way the needed data to be collected being extracted with some Excel Formulas in to another Worksheet.

MANAGED TO MAKE IT WORK!

But im now facing new issue O_O The thing is that the Worksheet I input data to is integrated with other type of data being collected from aother Source. NOT the FORM being discussed here. And it has some other Columns being filled in the same Worksheet.

Every Data type has its own Column and Some Columns with formulas.

For example TOTAL(Customer’s Debth) column formula is the following:

=(- “Price column” * “Hours column”) + Bank Transfer sum + Cash sum + Check sum + Credit Card sum.

The Header row contains SUM or AVERAGE of the whole Column.

Two more simple formulas in two more columns in the Worksheet.

The issue and the funny part: BEFORE I started testing (when got stuck on this Post’s original issue) it seems that combined Worksheet is working perfect.

Filling in the needed Data Columns ignoring the irrelevant columns. (depending on the Source Type data is being pulled from). At some point the filled forms seems to corrupt the Worksheet: Example, While a Form is Saved and Data send to the Worksheet: BEFORE

I touched something and corrupted its functional

The saved form was IGNORING the Columns that are irrelevant for the form, and not corrupting the FORMULA columns. NOW if i save a Form; the Columns that are irrelevant to the form is being filled in like if the CELL contains a Formula and showing ERROR in the Cell BECAUSE IT IS BLANK and the Header with SUM\AVERAGE formula as well get an error… although the cell is not supposed to contain data according to the FORM columns !!

Now neither the previous column order neither the new one is not working properly.

Any ideas what have i done ?

Cant figure it out.

Neither Hiding the Columns from the APP or making them Mandatory \ Not Mandatory is not canceling this issue…

[Steven_Coile]

So what I’ve gathered is you have a table with the following columns: PAYMENT METHOD, BANK TRANSFER, CASH, CHECK, CREDIT CARD, and CUSTOMER.

At first, you want only PAYMENT METHOD displayed until the user selects the payment method. Once a payment method is selected in PAYMENT METHOD, the corresponding price column should be displayed (either BANK TRANSFER, CASH, CHECK, or CREDIT CARD).

Once the appropriate price column is filled, the CUSTOMER column should be displayed.

The Editable_If property for the columns should be as follows:

BANK TRANSFER: =([PAYMENT METHOD] = “Bank Transfer”)

CASH: =([PAYMENT METHOD] = “Cash”)

CHECK: =([PAYMENT METHOD] = “Check”)

CREDIT CARD: =([PAYMENT METHOD] = “Credit Card”)

CUSTOMER: =OR(ISNOTBLANK([BANK TRANSFER]), ISNOTBLANK([CASH]), ISNOTBLANK([CHECK]), ISNOTBLANK([CREDIT CARD]))