Circular calc?

[Anuviel]

I have 3 fields:

InfiniteSupply_List (value list with choice of Yes, No)

Available (auto enter calc: If (InfiniteSupply_List = "Yes" ; "" ; Available) Do not replace value is unchecked)

InfiniteSupply (auto enter calc: If (InfiniteSupply_List = "Yes" ; 1 ; 0 ) Do not replace value is unchecked)

This is the work-flow:

The user will select Yes or No depending on if the supply is infinite or not. If Yes is chosen the Available field will be empty, even if they enter a number by mistake it will revert to empty due to calculation. At the same time InfiniteSupply will be set to 0 or 1 depending on InfiniteSupply_List. So far so good. It works nicely and as intended. The issue is when I import data. The file will be used almost exclusively for import of data, its modification and then export. Import file has two fields out of three mentioned. It has InfiniteSupply and Available. So when I import InfiniteSupply will have the value of 1 or 0. I need InfiniteSupply_List set to Yes if InfiniteSupply is 1 after the import or to No if InfiniteSupply is 0.

Reason: The file is made to handle online database. The user exports the information from their online database in .csv format, imports the data in FM, makes changes, exports and uploads. The FM is intended to make the handling of data more friendly and more simple for seasonal employees as the online DB is live and its interface is not friendly at all.

Thanks.,

[Fitch]

Use a calculated Replace on InfiniteSupply_List after you import:

Case( InfiniteSupply ; "Yes"; "No" )

PS: Seems like you have 3 fields where one would do.

[Anuviel]

Alright, will try. I think I need at least 2 fields. The online db does not use yes, no. It uses 0 and 1, 0 for no and 1 for yes. What I am trying is to replace that with words so a person without any experience or training can make changes easily.

Thanks.,

[Raybaudi]

I think that the simplest solution is to set the InfiniteSupply_List ( custom list of 0 and 1 )field as number and format that number field as boolean with "yes" when 1 and "no" when 0

[Anuviel]

You might be onto something. Will look into this. I need it to be Yes or No in selection but value needs to be 0 or 1. They hire a lot of high-school kids to handle data input around holidays so I need to make sure it is simple and user proof.

Thanks

[Raybaudi]

Changed my mind, a list from a custom table.

Look at this example...

( there is even a conditional formatting to never see any number )

Y_N.zip

[Anuviel]

Nice, think this will work nicely. Thanks

[Anuviel]

Actually it will not. I am back to the original issue when importing. Import file has 0 and 1 and if I import into the field the field that has the Yes, NO choice will not get updated.

[Raybaudi]

Try to import with this modified version into InfiniteSupply

( when asked, put a flag into the box: perform auto-enter options... )

Y_Nmod.zip

[Anuviel]

Thanks a lot. Works good with yes and no and with import.

Thanks.,